搬瓦工中文网
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | --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根据子关联来更新只要一条语句就可以搞定了，测试代码如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | --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 |
