22,207
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE TA(ID INT,VALUE INT)
CREATE TABLE TB(emp_id int,value int,expression varchar(100))
insert ta select 1,30 union all select 2,45 union all select 3 ,100
insert tb
select 1,null,'(select value from ta where id=2)' union all
select 2,null,'(select value from ta where id=1)'
-->更新
declare my_cursor cursor scroll dynamic for select expression,emp_id from tb
open my_cursor
declare @sql varchar(1000)
declare @exp varchar(100)
declare @empid int
fetch next from my_cursor into @exp,@empid
while(@@fetch_status=0)
begin
select @sql='update tb set value='+@exp+ ' where emp_id='+ltrim(@empid)
print @sql
exec(@sql)
fetch next from my_cursor into @exp,@empid
end
close my_cursor
deallocate my_cursor
-->查询
select * from tb
--drop table ta,tb
/**
emp_id value expression
----------- ----------- -----------------------------------------------
1 45 (select value from ta where id=2)
2 30 (select value from ta where id=1)
(所影响的行数为 2 行)
**/
declare @TABName varchar(8000)
declare @STR varchar(8000)
set @STR='update tb
set value=case when tb.emp_id in (1,3) then 基本工资 else 奖金 end
from tb join '+@TABName+'
on tb.emp_id='+@TABName+'.emp_id'
exec(@STR)
update tb set value=奖金
from tb join v_PAY_SALARY_ELEMENT
on tb.emp_id=v_PAY_SALARY_ELEMENT.emp_id
update tb
set value=case when tb.emp_id in (1,3) then 基本工资 else 奖金 end
from tb join v_PAY_SALARY_ELEMENT
on tb.emp_id=v_PAY_SALARY_ELEMENT.emp_id
update tb
set value=奖金
from tb join v_PAY_SALARY_ELEMENT
on tb.emp_id=v_PAY_SALARY_ELEMENT.emp_id