MySQL 更新两个表，其中它们的列等于第三个表中的行

Question

如标题所示，我尝试使用单个查询来更新两个不同表中的多行，其中 id 等于从第三个表中选择的结果。以下是示例表格布局。

CREATE TABLE hampster_families (
    id INT(13) NOT NULL AUTO_INCREMENT,
    family_name varchar(255) NOT NULL UNIQUE,
    fuzzy_name varchar(255),
    tags varchar(255),
    date_added TIMESTAMP NOT NULL DEFAULT NOW(),
        PRIMARY KEY (id)

CREATE TABLE apple_addresses (
    id INT(13) NOT NULL AUTO_INCREMENT,
    address VARCHAR(39) NOT NULL UNIQUE,
    monitor BOOLEAN NOT NULL DEFAULT 0,
    date_added TIMESTAMP NOT NULL DEFAULT NOW(),
    hampster_family int(7) NOT NULL DEFAULT 0,
        PRIMARY KEY (id)

CREATE TABLE orange_addresses (
    id INT(13) NOT NULL AUTO_INCREMENT,
    address varchar(200) NOT NULL UNIQUE,
    monitor BOOLEAN NOT NULL DEFAULT 0,
    date_added TIMESTAMP NOT NULL DEFAULT NOW(),
    hampster_family INT(7) NOT NULl DEFAULT 0,
        PRIMARY KEY (id)

我正在尝试将 orange_addresses.monitor、apple_addresses.monitor 更新为等于 1，其中 orange_addresses.hampster_family、apple_addresses.hampster_family 等于从 SELECT id FROM hampster_families WHERE hampster_fammilies.family_name = "African Rabid"返回的 ID

我已经研究过使用存储变量，但是(我缺乏 SQL 能力在这里闪耀)因为这个查询需要一个变量，我认为存储变量不起作用。鉴于此，我研究过使用连接来完成此操作，但我从根本上误解了 UPDATE/JOIN 组合的语法。因此，我在这里寻找一些启示! :)

Answer 1

一步一步:这是您的基本查询:

 SELECT id 
 FROM hampster_families 
 WHERE hampster_fammilies.family_name = "African Rabid"

现在，您需要所有 orange_addresses，其中 orange_addresses.hampster_family 是该 ID，因此，加入:

 SELECT hampster_families.id 
 FROM hampster_families
 LEFT JOIN orange_adresses
     ON orange_addresses.hampster_family = hampster_families.id
 WHERE hampster_fammilies.family_name = "African Rabid"

apple_addresses 也是如此:

 SELECT hampster_families.id 
 FROM hampster_families
 LEFT JOIN orange_adresses
     ON orange_addresses.hampster_family = hampster_families.id
 LEFT JOIN apple_adresses
     ON apple_addresses.hampster_family = hampster_families.id
 WHERE hampster_fammilies.family_name = "African Rabid"

我们对他们的监控栏很感兴趣

 SELECT orange_addresses.monitor, apple_addresses.monitor
 FROM hampster_families
 LEFT JOIN orange_adresses
     ON orange_addresses.hampster_family = hampster_families.id
 LEFT JOIN apple_adresses
     ON apple_addresses.hampster_family = hampster_families.id
 WHERE hampster_fammilies.family_name = "African Rabid"

使其成为更新:将选定的字段移动到集合之后，使 SELECT FROM an UPDATE:

 UPDATE hampster_families
 LEFT JOIN orange_adresses
     ON orange_addresses.hampster_family = hampster_families.id
 LEFT JOIN apple_adresses
     ON apple_addresses.hampster_family = hampster_families.id
 SET 
   orange_addresses.monitor = 1
   apple_addresses.monitor  = 1
 WHERE hampster_fammilies.family_name = "African Rabid"