MLGB
微软的sqlserver有一个BUG,在使用表变量进行修改时,他会提醒你:
必须声明标量变量 "@tempTable"。
首先
/*create table test
(
id int identity(1,1),
name varchar(10)
)
insert into test
select '222'
union select '444'
union select '455'
*/
go
declare @mytabe table(
id int,
name varchar(10)
)
insert into @mytabe
select 1,'123'
union select 2,'123'
union select 4,'123'
union select 6,'123'
-- 删除
delete from test
where id not in
(
select id from @mytabe
)
--(4 行受影响)
--(1 行受影响)
-- 增加
insert into test
select name from @mytabe
--(4 行受影响)
--修改
update test set test.name= @mytabe.name
from @mytabe
where test.id=@mytabe.id
--必须声明标量变量 "@mytabe"。
--解决方法
update test set test.name= ta.name
from @mytabe as ta
where test.id=ta.id
--(4 行受影响)