Example of updating records using update join in MSSQL

Data Preparation

The code is as follows:	Copy code
Create table tab1 (name nvarchar (20), gold int) Create table tab2 (name nvarchar (20), gold int) -- Data Insert into tab1 values ('user1', 10) Insert into tab1 values ('user2', 10) Insert into tab2 values ('user1', 20)

2. update join update record

The code is as follows:	Copy code
Update tab1 set gold = t1.gold + t2.gold from tab1 t1 inner join tab2 t2 on t1.name = t2.name

Note: the data of the two tables must be in a one-to-one relationship. Otherwise, the update results may be unclear.

Two sentences sorted online

1. Add back the original consumption amount

The code is as follows:	Copy code
UPDATE e SET e. money = e. money + d. amount FROM employee e Inner join (SELECT empid, amount = sum (amount) FROM deleted WHERE rechargeable = 1 group by empid) d ON d. empid = e. id

      
2. Deduct new consumption amount

The code is as follows:	Copy code
UPDATE e SET e. money = e. money-I. amount FROM employee e Inner join (SELECT empid, amount = sum (amount) FROM inserted WHERE rechargeable = 1 group by empid) I ON I. empid = e. id