--书名和价格相同的记录
with a as
(
select * from tbookinfo as t1,tbookinfo2 as t2 where t1.fbookname=t2.booktitle and t1.fbookpurchasemoney=t2.bookprice
)declare @i int
set @i=3020
while @i<7181
begin
if @i=select fbookid from a where fbookid=@i
update tbookinfo2 set id=@i
set @i=@i+1
else
set @i=@i+1
end
消息 156,级别 15,状态 1,第 7 行
关键字 'declare' 附近有语法错误。
消息 156,级别 15,状态 1,第 11 行
关键字 'select' 附近有语法错误。
消息 156,级别 15,状态 1,第 14 行
关键字 'else' 附近有语法错误。
with a as
(
select * from tbookinfo as t1,tbookinfo2 as t2 where t1.fbookname=t2.booktitle and t1.fbookpurchasemoney=t2.bookprice
)declare @i int
set @i=3020
while @i<7181
begin
if @i=select fbookid from a where fbookid=@i
update tbookinfo2 set id=@i
set @i=@i+1
else
set @i=@i+1
end
消息 156,级别 15,状态 1,第 7 行
关键字 'declare' 附近有语法错误。
消息 156,级别 15,状态 1,第 11 行
关键字 'select' 附近有语法错误。
消息 156,级别 15,状态 1,第 14 行
关键字 'else' 附近有语法错误。
from tbookinfo as t1,tbookinfo2 as t2 where t1.fbookname=t2.booktitle and t1.fbookpurchasemoney=t2.bookprice
declare @i int
set @i=3020
while @i<7181
begin
if @i=select fbookid from #a where fbookid=@i
update tbookinfo2 set id=@i
set @i=@i+1
else
set @i=@i+1
end
from tbookinfo as t1,tbookinfo2 as t2 where t1.fbookname=t2.booktitle and t1.fbookpurchasemoney=t2.bookprice
declare @i int
set @i=3020
while @i<7181
begin
if @i=(select fbookid from #a where fbookid=@i)
begin
update tbookinfo2 set id=@i
set @i=@i+1
end
else
set @i=@i+1
end
set @i=3020
while @i<7181
begin
if @i=select fbookid from a where fbookid=@i直接增删查改。
with a as
(
select * from tbookinfo as t1,tbookinfo2 as t2 where t1.fbookname=t2.booktitle and t1.fbookpurchasemoney=t2.bookprice
)declare @i int
set @i=3020
while @i<7181
begin
/**with a as
(
select * from tbookinfo as t1,tbookinfo2 as t2 where t1.fbookname=t2.booktitle and t1.fbookpurchasemoney=t2.bookprice
)**/--with定义之后必须在下一句运行 ,其实不须定义with,你可以插入临时表 或者直接写
--if @i=select fbookid from a where fbookid=@i --改写如下
if exists(select tbookid from tbookinfo as t1,tbookinfo2 as t2 where t1.fbookname=t2.booktitle and t1.fbookpurchasemoney=t2.bookprice and fbookid=@i ) --这样最好了
update tbookinfo2 set id=@i
set @i=@i+1
else
set @i=@i+1
end