Pass column update from one table to another

Source: Internet
Author: User
Tags microsoft sql server one table
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
Create table # t1 (c1 int not null, c2 char (5), c3 char (5 ),
C4 char (5 ))
Create table # t2 (c1 int not null, c2 char (5), c3 char (5 ),
C4 char (5 ))
-- 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
-Microsoft SQL Server Development team

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.