MySQL update multiple tables with one query

Question

I have a function that updates three tables  but I use three queries to perform this  I wish to use a more convenient approach for good practice   How can I update multiple tables in MySQL with a single query

User · Answer

When you say multiple queries do you mean multiple SQL statements as in:

UPDATE table1 SET a=b WHERE c;
UPDATE table2 SET a=b WHERE d;
UPDATE table3 SET a=b WHERE e;

Or multiple query function calls as in:

mySqlQuery(UPDATE table1 SET a=b WHERE c;)
mySqlQuery(UPDATE table2 SET a=b WHERE d;)
mySqlQuery(UPDATE table3 SET a=b WHERE e;)

The former can all be done using a single mySqlQuery call if that is what you wanted to achieve, simply call the mySqlQuery function in the following manner:

mySqlQuery(UPDATE table1 SET a=b WHERE c; UPDATE table2 SET a=b WHERE d; UPDATE table3 SET a=b WHERE e;)

This will execute all three queries with one mySqlQuery() call.

User · Answer

Let s say I have Table1 with primary key  id and a boolean column doc availability  Table2 with foreign key  id and DateTime column last update and I want to change the availability of a document with  id 14 in Table1 to 0 i e unavailable and update Table2 with the timestamp when the document was last updated  The following query would do the task  UPDATE Table1  Table2  SET doc availability   0  last update   NOW    WHERE Table1  id   Table2  id AND Table1  id   14

User · Answer

Take the case of two tables  Books and Orders  In case  we increase the number of books in a particular order with Order ID   1002 in Orders table then we also need to reduce that the total number of books available in our stock by the same number in Books table  UPDATE Books  Orders SET Orders Quantity   Orders Quantity   2      Books InStock   Books InStock - 2 WHERE     Books BookID   Orders BookID     AND Orders OrderID   1002

User · Answer

You can also do this with one query too using a join like so   UPDATE table1 table2 SET table1 col a table2 col2 b WHERE items id month id    And then just send this one query  of course  You can read more about joins here  http   dev mysql com doc refman 5 0 en join html  There s also a couple restrictions for ordering and limiting on multiple table updates you can read about here  http   dev mysql com doc refman 5 0 en update html  just ctrl f  join

User · Answer

UPDATE t1 INNER JOIN t2 ON t2 t1 id   t1 id INNER JOIN t3 ON t2 t3 id   t3 id SET t1 a    something       t2 b   42      t3 c   t2 c WHERE t1 a    blah     To see what this is going to update  you can convert this into a select statement  e g    SELECT t2 t1 id  t2 t3 id  t1 a  t2 b  t2 c AS t2 c  t3 c AS t3 c FROM t1 INNER JOIN t2 ON t2 t1 id   t1 id INNER JOIN t3 ON t2 t3 id   t3 id WHERE t1 a    blah     An example using the same tables as the other answer   SELECT Books BookID  Orders OrderID      Orders Quantity AS CurrentQuantity      Orders Quantity   2 AS NewQuantity      Books InStock AS CurrentStock      Books InStock - 2 AS NewStock FROM Books INNER JOIN Orders ON Books BookID   Orders BookID WHERE Orders OrderID   1002   UPDATE Books INNER JOIN Orders ON Books BookID   Orders BookID SET Orders Quantity   Orders Quantity   2      Books InStock   Books InStock - 2 WHERE Orders OrderID   1002    EDIT   Just for fun  let s add something a bit more interesting   Let s say you have a table of books and a table of authors  Your books have an author id  But when the database was originally created  no foreign key constraints were set up and later a bug in the front-end code caused some books to be added with invalid author ids  As a DBA you don t want to have to go through all of these books to check what the author id should be  so the decision is made that the data capturers will fix the books to point to the right authors  But there are too many books to go through each one and let s say you know that the ones that have an author id that corresponds with an actual author are correct  It s just the ones that have nonexistent author ids that are invalid  There is already an interface for the users to update the book details and the developers don t want to change that just for this problem  But the existing interface does an INNER JOIN authors  so all of the books with invalid authors are excluded   What you can do is this  Insert a fake author record like  Unknown author   Then update the author id of all the bad records to point to the Unknown author  Then the data capturers can search for all books with the author set to  Unknown author   look up the correct author and fix them   How do you update all of the bad records to point to the Unknown author  Like this  assuming the Unknown author s author id is 99999    UPDATE books LEFT OUTER JOIN authors ON books author id   authors id SET books author id   99999 WHERE authors id IS NULL    The above will also update books that have a NULL author id to the Unknown author  If you don t want that  of course you can add AND books author id IS NOT NULL

User · Answer

That s usually what stored procedures are for  to implement several SQL statements in a sequence  Using rollbacks  you can ensure that they are treated as one unit of work  ie either they are all executed or none of them are  to keep data consistent

[mysql] MySQL, update multiple tables with one query

Examples related to mysql

Examples related to sql

Examples related to sql-update