Multiple table associations update multiple different records at the same time

Because the project requires that multiple different records be updated at the same time, we have discussed a good solution with our colleagues.

The following is a test example.

1. Create two temporary tables and input test data:

View code

 1 create table #temptest1
 2 (
 3     id int,
 4     name1 varchar(50),
 5     age int
 6 )
 7 
 8 create table #temptest2
 9 (
10     id int,
11     name1 varchar(50),
12     age int
13 )

The following table data is queried:

# Temptest1 # temptest2

 

2. Now we need to update the age in # temptest2 to the corresponding age in # temptest1.

In fact, the age of Id 1 in [Table 1] is changed to 19, and the age of ID 2 is changed to 20.

Of course, the requirements here are:Use only one SQL statement, and do not use Loops.

The result is as follows:

 

The implementation method is as follows:

Update T1

Set T1. Age = t2.age

From # temptest1 T1

Join # temptest2 T2

On t1.id = t2.id

 

(Supplement) SQL Server 2008 merge command syntax:

Merge into # temptest1 T1
Using (select age, ID from # temptest2) T2
On t1.id = t2.id
When matched then
Update set t1.age = t2.age

 

Is it an interesting SQL statement.

There is also a related blog for your reference:☜Tracy☞

--------------------------------------------------------------