sql - MySQL, update multiple tables with one query


Translate

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?


All Answers
  • Translate

    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;
    

  • Translate
    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;
    

  • Translate

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


  • Translate

    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.


  • Translate

    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.