UPDATE joins in MySQL

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!

Syntax

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.

This entry was posted in Join Tutorials and tagged , , . Bookmark the permalink.

4 Responses to UPDATE joins in MySQL

  1. Pingback: Log Buffer #242, A Carnival of the Vanities for DBAs | The Pythian Blog

  2. Pingback: UPDATE joins in MySQL | MySQL | Syngu

  3. roopali says:

    plz provide details of self join also….

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>