SQL Update Summary

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