????? Update是T-sql中再簡單不過的語句了，update table set column=expression??[where condition]，我們都會用到。但update的用法不僅于此，真正在開發的時候，靈活恰當地使用update可以達到事半功倍的效果。
????? 假定有表Table1（a,b,c）和Table2（a,c)，現在Table1中有些記錄字段c為null，要根據字段a在Table2中查找，取出字段a相等的字段c的值來更新Table1。一種常規的思路，通過游標遍歷Table1中字段c為null的所有記錄，在循環體內查找Table2并進行更新，即用游標Cursor的形式。測試sql語句如下：
使用游標遍歷方式更新????--1.創建測試表
????create?TABLE?Table1
????(
????????a?varchar(10),
????????b?varchar(10),
????????c?varchar(10),
????????CONSTRAINT?[PK_Table1]?PRIMARY?KEY?CLUSTERED
????????(
????????????a?ASC
????????)
????)?ON?[PRIMARY]
????create?TABLE?Table2
????(
????????a?varchar(10),
????????c?varchar(10),
????????CONSTRAINT?[PK_Table2]?PRIMARY?KEY?CLUSTERED
????????(
????????????a?ASC
????????)
????)?ON?[PRIMARY]
????GO
????--2.創建測試數據
????Insert?into?Table1?values('趙','asds',null)
????Insert?into?Table1?values('錢','asds','100')
????Insert?into?Table1?values('孫','asds','80')
????Insert?into?Table1?values('李','asds',null)
????Insert?into?Table2?values('趙','90')
????Insert?into?Table2?values('錢','100')
????Insert?into?Table2?values('孫','80')
????Insert?into?Table2?values('李','95')
????GO
????select?*?from?Table1
????--3.通過游標方式更新
????declare?@name?varchar(10)
????declare?@score?varchar(10)
????declare?mycursor?cursor?for?select?a?from?Table1?where?c?is?null
????open?mycursor
????fetch?next?from?mycursor?into?@name
????while(@@fetch_status?=?0)
????BEGIN
????????select?@score=c?from?Table2?where?a=@name
????????update?Table1?set?c?=?@score?where?a?=?@name
????????fetch?next?from?mycursor?into?@name????
????END
????close?mycursor
????deallocate?mycursor
????GO
????--4.顯示更新后的結果
????select?*?from?Table1
????GO
????--5.刪除測試表
????drop?TABLE?Table1
????drop?TABLE?Table2
?
???? 雖然用游標可以實現，但代碼看起來很復雜，其實用Update根據子關聯來更新只要一條語句就可以搞定了，測試代碼如下：
使用帶關聯子查詢的Update更新????--1.創建測試表
????create?TABLE?Table1
????(
????????a?varchar(10),
????????b?varchar(10),
????????c?varchar(10),
????????CONSTRAINT?[PK_Table1]?PRIMARY?KEY?CLUSTERED
????????(
????????????a?ASC
????????)
????)?ON?[PRIMARY]
????create?TABLE?Table2
????(
????????a?varchar(10),
????????c?varchar(10),
????????CONSTRAINT?[PK_Table2]?PRIMARY?KEY?CLUSTERED
????????(
????????????a?ASC
????????)
????)?ON?[PRIMARY]
????GO
????--2.創建測試數據
????Insert?into?Table1?values('趙','asds',null)
????Insert?into?Table1?values('錢','asds','100')
????Insert?into?Table1?values('孫','asds','80')
????Insert?into?Table1?values('李','asds',null)
????Insert?into?Table2?values('趙','90')
????Insert?into?Table2?values('錢','100')
????Insert?into?Table2?values('孫','80')
????Insert?into?Table2?values('李','95')
????GO
????select?*?from?Table1
????--3.通過Update方式更新
????Update?Table1?set?c?=?(select?c?from?Table2?where?a?=?Table1.a)?where?c?is?null
????GO
????--4.顯示更新后的結果
????select?*?from?Table1
????GO
????--5.刪除測試表
????drop?TABLE?Table1
????drop?TABLE?Table2
?
????? 參考資料：也許是被忽略的update語句，update 子查詢
)
)
)
