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

Tweet

******IMPORTANT NOTE******
Only now after a few years of comments, I understand the big confusion I generated, and I understand it's been my fault and for that I do apologize.
The query works but NOT in MSSMS: the query works in ASP or in a Ms Access environment with linked tables to MS SQL. I hope this clarify a bit...


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.

The 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

The advantages?

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.