Can I update two identical tables in with one query - mySQL

Question

Can I update two identical tables with one query?

TABLEA
_____________________________
|     id      |   value     |
|_____________|_____________| 
|      1      |      a      | 
|      2      |      b      | 
|      3      |      c      | 
|      4      |      d      | 
|      5      |      e      |    
|_____________|_____________| 

TABLEB
_____________________________
|     id      |   value     |
|_____________|_____________| 
|      1      |      a      | 
|      2      |      b      | 
|      3      |      c      | 
|      4      |      d      | 
|      5      |      e      |    
|_____________|_____________| 

I want to update both tables (set value = 'z' WHERE id=3) at the same time. Is this possible?

-Thanks

Answer 1

by reading mysql update reference, it says:

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

http://dev.mysql.com/doc/refman/5.0/en/update.html

Answer 2

MySQL supports multiple-table updates using the following syntax:

UPDATE tablea, tableb
SET    tablea.value = 'z', tableb.value = 'z'
WHERE  (tablea.id = tableb.id) AND (tablea.id = '3');

Test case:

CREATE TABLE tablea (id int, value char(1));
CREATE TABLE tableb (id int, value char(1));

INSERT INTO tablea VALUES (1, 'a');
INSERT INTO tablea VALUES (2, 'b');
INSERT INTO tablea VALUES (3, 'c');
INSERT INTO tablea VALUES (4, 'd');
INSERT INTO tablea VALUES (5, 'e');

INSERT INTO tableb VALUES (1, 'a');
INSERT INTO tableb VALUES (2, 'b');
INSERT INTO tableb VALUES (3, 'c');
INSERT INTO tableb VALUES (4, 'd');
INSERT INTO tableb VALUES (5, 'e');

Result:

SELECT * FROM tablea;
+------+-------+
| id   | value |
+------+-------+
|    1 | a     |
|    2 | b     |
|    3 | z     |
|    4 | d     |
|    5 | e     |
+------+-------+
5 rows in set (0.00 sec)

SELECT * FROM tableb;
+------+-------+
| id   | value |
+------+-------+
|    1 | a     |
|    2 | b     |
|    3 | z     |
|    4 | d     |
|    5 | e     |
+------+-------+
5 rows in set (0.00 sec)

---

UPDATE:

If you prefer not to repeat the value you are going to set twice, you may want to use the following trick:

UPDATE tablea, tableb, (SELECT 'z' val) d
SET    tablea.value = d.val, tableb.value = d.val
WHERE  (tablea.id = tableb.id) AND (tablea.id = '3');

Answer 3

It's possible to update them at the same time, but not with a single SQL statement (okay, it is, but it's not recommended). This is pretty much the whole point of transactions: you update each table individually, but those changes don't take effect until you commit. To any other user, the tables updated simultaneously.

If you're really insistent on updating both in one statement, the following might work, but it depends on both tables having ID as the primary key and may depend on the specific RDBMS you're using:

update (select tablea.id, 
               tablea.value v1, 
               tableb.value v2 
        from tablea 
             join tableb 
             on tablea.id = tableb.id)
set v1 = 'z'
where id = 3

Answer 4

Most RDBMS only allow you to insert, update or delete directly on one table at a time. If you have foreign keys and cascade then some inserts and deltes can go on more than one table

However if you are using a relational database you should not be having two tables that need the same update. One driver behing relational databases is data integrity that a relation is held in only one way.e/g/ in this case if id of 3 means that value is c (or z after a chnage) the relation should only be held in one place