Q: How can I use three columns in Table B to UPDATE three columns in Table A in an UPDATE statement?
A: You can use powerful relational algebra to solve this problem. The code on this page illustrates how to use the FROM clause and JOIN operations in combination to update specified columns with data in other tables. When designing a relational expression, you need to determine whether a single row needs to match multiple rows (one-to-multiple relationships ), or multiple rows must match a single row in the joined table to update all rows (multiple-to-one relationship ).
In a one-to-multiple relationship, SQL Server always uses the last row it finds to update data. However, you cannot control the location of the last row. On a multi-processor computer, the query may be performed synchronously, and the locations of the last row of the same query may be different. Therefore, Microsoft recommends that you do not use one-to-many relationships.
If the table to be updated is the same as the table in the FROM clause and the FROM clause only contains a reference to the table, the alias may not be specified. If the table to be updated appears multiple times in the FROM clause, only one reference of the table can omit the alias of the table. All other references to the table must contain one table alias.
USE tempdb
GO
Create table # t1 (c1 int not null, c2 char (5), c3 char (5 ),
C4 char (5 ))
GO
Create 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', 'all', '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 result
SELECT * FROM # t1
-Microsoft SQL Server Development team
