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