SQL Update Summary

Source: Internet
Author: User

The previous update with a lot of, but are simple single-table operation, no care, recently consulted Multiple Table Association update and update top N, found that the update is really flexible, recorded as follows (test pass under MSSQLSERVER2008R2):

1 Single-table operation

Update table1 set Col1=val[,col2=val2 ...]

[Where Condition expression]

2 Multi-Table association operations

1) Update table name set column name from Table 1, table 2 where condition, this method source table (table1) cannot use as Alias, long table name is troublesome, as follows:

Update table1 Set Col1=table2.col1

From Table2 where Table1.pkey=table2.pkey

2) Use Alias table update, Concise!

Update T1 set Col1=t2.col1

From table1 t1,table2 T2 where T1.pkey=t2.pkey

3) Update from query table

Update T1 set Col1=val

From (SELECT * from table1 [where conditional expression]) T1

Application: Update Top N

Update T1 set Col1=val

From (select Top ten * from table1 [where conditional expression] order BY column N) t1

In addition, the query can be a table in this library, or it can be a foreign library table, such as a dblink connected table or OPENROWSET, etc.

4) using a CTE

; with-T as (SELECT * FROM table1 [where conditional expression])

Update T set Col1=val

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: info-contact@alibabacloud.com 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.