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.

ASP: conditional update or insert record

Tweet

There are situations when we need to insert or update a record in a table according to its presence. The condition is: if the record is already in the table, then update it, otherwise insert it.

Here we will see how to do it.

Some assumptions

To achive our goal, the first task is to determine which is the condition upon which we decide to update or insert the record.
I will show you an example, that will apply to a specific situation, however, after understanding the logic, you can think of something related to your personal goal.
In the following example, we have a table (table1) with 3 columns: id, field1 and field2. We have a form submitting data. Then there's another table (table2), linked to the first table through the id field. The columns of this second table are: id and field3.
The first table (table1) might have records linked to the second table (table2), while table2 already contains records. That is a classic situation in relational databases. The point here is that we know that a unique record is present in table2, and we want to insert a new unique record in table1 if it's not already present, otherwise we need to update it.
I hope that the above explanation is clear enough.