Q: How do I update the three columns in Table A in a UPDATE statement?
A: For this question, you can use a powerful relationship algebra. The code in this page illustrates how to combine the from clauses and JOIN operations to achieve the purpose of using the data update specified in other tables. When designing a relationship expression, you need to decide whether you need a single row to match multiple rows (a pair of relationships), or you need multiple rows to match single rows in the coupling table to update all rows (multiple pairs).
In a pair of multiple relationships, SQL Server always updates data using the last line it finds. However, you cannot control the location where the last line is located. On a multiprocessor's computer, the query may be synchronous, the location of the same query may be different. Therefore, Microsoft recommends try not to use a pair of relationships.
If the table to be updated is the same as the table in the FROM clause, and the from clause only contains one reference to the table, the alias may not be specified. If the table to be updated has been repeated in the FROM clause, only one reference to the table can omit the alias, and all other references to the table must contain a form name.
Use Tempdbgocreate Table # T1 (C1 Int Not Null, C2 Char (5), C3 Char (5), C4 Char (5)) GOCREATE TABLE # T2 (C1 Int Not Null, C2 Char (5), C3 Char (5) , C4 char (5)) GO
- Data assignment INSERT # T1 VALUES (1, 'Hello', 'there', 'Fred') Insert # T2 VALUES (1, 'How', 'Are', 'YOU?')
- Update data Update # T1 set # t1.c2 = # t2.c2, # t1.c3 = # t2.c3, # t1.c4 = # t2.c4 from # T2 where # t1.c1 = # t2.c1
- Check results Select * from # T1
-Microsoft SQL Server Development Team
