Blogging Tips

SQL: how to update two (or more) tables at the same time

Sometimes, when searching for an answer, we end up making things too much complicated, while easy solutions are just round the corner. This is the case of a simple task like updating two related tables with just one SQL query.

Suppose we have two related tables. The first contains user names, and the second email addresses related to the first table names.

First table ("names")

ID	name
1	John
2	James

The second table ("addresses")

ID	address
1	First Street
2	Second Street

How do we change the name and the street of the first record (with id equal to 1)?
With one simple query.


When we need to update one table, we usually perform a single SQL query like:

UPDATE
names
SET
name = 'Peter'
WHERE
ID = 1

Simple as that.
But if we want to update the two tables at the same time?
We can use a JOIN based on the ID field.

UPDATE
names
INNER JOIN
addresses
ON
names.ID = addresses.ID
SET
names.name = 'Peter', addresses.address = 'Third Street'
WHERE
names.ID = 1

Well, there is an immediate advantage in performing just a single SQL query instead of two, and I believe it is quite clear: the server will have a lighter work load.
At the same time, we will have a full control on the performed operation, which will be faster and easier to maintain.

Sometimes, solutions are easier than thought, and I believe this is the case.