When you want to update rows that are stored in MySQL tables you use update statements. Usually the table you’re updating has to be defined and in case you want to update only certain rows a condition for the update operation has to be specified. If the condition can be expressed based on columns of the same table, you most probably do this by adding a WHERE-clause. But what if there are rows in tableA which you want to update based on a condition that depends on related rows from tableB? The answer? Drumroll please…: Use a join as you would do in SELECT statements!
When you’re updating data in MySQL, you can specify a condition to specify which rows should be affected. The following update join example makes use of an inner join. However, you can use any other join operation MySQL supports, like an outer join for example, as well. In the following example we’re going to raise prices for all books written by Steve Jobs who recently passed away (R.I.P. Steve!):
UPDATE b SET price = price + 5 FROM book AS b INNER JOIN author a ON b.id = a.book_id WHERE a.name LIKE 'Steve Jobs'
This example is based on a simple book database. Books are stored in one table, and authors are stored in another one. One book can have more than one author. The one-to-many relationship (1:n)between the two tables is represented by the column “book_id” in the authors table. Of course, on author can write more than one book as well, but I simplified the example for the purpose of this tutorial.