CREATE PROCEDURE proc_update AS
begin
declare cur_update cursor dynamic for select * from customer_cmb
open cur_update
fetch first from cur_update
while @@fetch_status=0
begin
update customer_cmb set age = (case
when substring(id,7,4)like '%[a-zA-Z]%' then '0'
when len(id)=18 then year(getdate())-substring(id,7,4)
when len(id)=15 then year(getdate())-('19'+substring(id,7,2))
else '0'
end ) where current of cur_update
fetch next from cur_update
end
close cur_update
deallocate cur_update
end
存储过程中使用了游标，数据库是 sql2000, win7系统，问题RT。。。
望各位达人们帮帮忙...
------解决方案--------------------
更新这么大的数据量 数据会锁表的
即便不用游标也会花费大量的时间。切忌在生产环境中执行。
分批次执行更新吧
------解决方案--------------------
- SQL code
直接执行不行吗：update customer_cmb set age = (casewhen substring(id,7,4)like '%[a-zA-Z]%' then '0'when len(id)=18 then year(getdate())-substring(id,7,4)when len(id)=15 then year(getdate())-('19'+substring(id,7,2))else '0' end )