Update是T-sql中再簡(jiǎn)單不過(guò)的語(yǔ)句了，update table set column=expression [where condition]，我們都會(huì)用到。但update的用法不僅于此，真正在開(kāi)發(fā)的時(shí)候，靈活恰當(dāng)?shù)厥褂胾pdate可以達(dá)到事半功倍的效果。
假定有表Table1（a,b,c）和Table2（a,c)，現(xiàn)在Table1中有些記錄字段c為null，要根據(jù)字段a在Table2中查找，取出字段a相等的字段c的值來(lái)更新Table1。一種常規(guī)的思路，通過(guò)游標(biāo)遍歷Table1中字段c為null的所有記錄，在循環(huán)體內(nèi)查找Table2并進(jìn)行更新，即用游標(biāo)Cursor的形式。測(cè)試sql語(yǔ)句如下：
--1.創(chuàng)建測(cè)試表
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.創(chuàng)建測(cè)試數(shù)據(jù)
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.通過(guò)游標(biāo)方式更新
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.顯示更新后的結(jié)果
select * from Table1
GO
--5.刪除測(cè)試表
drop TABLE Table1
drop TABLE Table2
雖然用游標(biāo)可以實(shí)現(xiàn)，但代碼看起來(lái)很復(fù)雜，其實(shí)用Update根據(jù)子關(guān)聯(lián)來(lái)更新只要一條語(yǔ)句就可以搞定了，測(cè)試代碼如下：
--1.創(chuàng)建測(cè)試表
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.創(chuàng)建測(cè)試數(shù)據(jù)
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.通過(guò)Update方式更新
Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null
GO
--4.顯示更新后的結(jié)果
select * from Table1
GO
--5.刪除測(cè)試表
drop TABLE Table1
drop TABLE Table2
參考資料：也許是被忽略的update語(yǔ)句，update 子查詢
