MySQL, update multiple tables with one query

by

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;

More Related Contents:

  1. UPDATE multiple rows with different values in one query in MySQL
  2. Multiple Updates in MySQL
  3. mysql update column with value from another table
  4. update multiple rows using limit in mysql?
  5. MySQL/SQL: Update with correlated subquery from the updated table itself
  6. What are differences between INSERT and UPDATE in MySQL?
  7. MySql – Way to update portion of a string?
  8. MySQL UPDATE syntax with multiple tables using WHERE clause
  9. The best way to remove value from SET field?
  10. Update MySQL with if condition
  11. Update multiple rows with multiple ‘where’ clauses for each individual row
  12. MySQL update statement to store ranking positions
  13. SQL select only rows with max value on a column [duplicate]
  14. How to create a MySQL hierarchical recursive query?
  15. How to reset AUTO_INCREMENT in MySQL
  16. MySQL Query GROUP BY day / month / year
  17. Join between tables in two different databases?
  18. How to select a column name with a space in MySQL [duplicate]
  19. MySQL error code: 1175 during UPDATE in MySQL Workbench
  20. How do I use on delete cascade in mysql?
  21. MySQL JOIN the most recent row only?
  22. MySQL Group By and Sum total value of other column
  23. What does SQL clause “GROUP BY 1” mean?
  24. Syntax error when using except in a query
  25. sql like operator on integer
  26. MySQL – SELECT * INTO OUTFILE LOCAL ?
  27. How to select non “unique” rows
  28. Generate serial number in mysql query
  29. What are the benefits of only_full_group_by mode?
  30. mySQL SELECT upcoming birthdays

Leave a Comment