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?

Answer 1

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;

Answer 2

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.

Answer 3

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").

Answer 4

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.

Answer 5

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.

Answer 6

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