CODEWITHSUNDEEP
mysql
geudrik
geudrik

Reputation: 672

MySQL update two tables where their columns equal that of a row from a third table

As the title suggests, I'm trying to use a single query to update multiple rows, in two distinct tables where an id is equal to the result of a select from a third table. Below are sample table layouts.

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)

I'm trying to UPDATE orange_addresses.monitor, apple_addresses.monitor to equal 1, where orange_addresses.hampster_family, apple_addresses.hampster_family equals the id returned from SELECT id FROM hampster_families WHERE hampster_fammilies.family_name = "African Rabid"

I've looked at using a stored variable, but (my lack of SQL prowess shining through here) because this query requires a variable, I don't think a stored variable would work. Given that, I've looked into using a join to accomplish this, but I'm fundamentally misunderstanding the syntax of an UPDATE/JOIN combo. As such, I'm here looking for some enlightenment! :)

Upvotes: 0

Views: 570

Answers (2)

Wrikken
Wrikken

Reputation: 70520

Step by step: This is your base query:

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

Now, you want all orange_addresses where orange_addresses.hampster_family is that id, so, JOIN:

 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"

The same goes for 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"

We are interested in their monitor columns

 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"

Make it an update: move the selected fields after the set, make 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"

Upvotes: 2

Jakub Matczak
Jakub Matczak

Reputation: 15686

This works for me. Just checked.

UPDATE orange_addresses, apple_addresses
SET orange_addresses.monitor=1,apple_addresses.monitor=1
WHERE orange_addresses.hampster_family = apple_addresses.hampster_family
    AND orange_addresses.id = (
        SELECT id 
        FROM hampster_families 
        WHERE hampster_families.family_name = 'Test' 
        LIMIT 1);

Upvotes: 1

Related Questions