Question

sqlserver 批量更新某些行数据等于另一行数据，求教

如图上的数据，想要根据相同的dmlx数据来查询，把pic和place设置更新为有数据的那一行的两个值；
例如dmlx=2的有两行，那么下面pic为0及place为空的那一行更新为pic='201410191001.jpg'，place也同样更新为“德阳市”，下面的数据同理，有几行就更新几行

Answer 1

create procedure [dbo].[p_updateInfo]
as
begin
declare @tableName varchar(100),
@intitNum int,
@maxCountNum int,
@dmls int,
@pic varchar(15),
@place varchar(15),
@strsql NVARCHAR(1000)

begin
select identity(int,1,1) as ids,* into #temp1 from aab

select * into #temp2 from aab
where pic !=0 and place is not null
select @intitNum = 1
select @maxCountNum = count(*) from #temp2
while(@intitNum <= @maxCountNum)
begin
select @strsql = N'select ' + @dmls + ' = dmlx '+ @pic + ' = pic '
+ @place + ' = place from aab where ids =' + @intitNum
print @strsql

--print @strsql
--exec　sp_executesql(@strsql)
exec sp_executesql @strsql,N'@dmls varchar output,@place varchar output,@dmls int output', @dmls = @dmls output,@place = @place output,@dmls = @dmls output
-- select @dmls = dmlx,@pic =pic,@place = place from #temp2 where ids = @intitNum
--select @strsql = N'update #temp1 set pic = ' + @pic+ ' ,place = '+@place + ' where dmlx = ' + @dmls
--update #temp1 set pic = @pic,place = @place where dmlx = @dmls
--exec sp_executesql @strsql
update #temp1 set pic = @pic,place = @place where dmlx = @dmls
select @intitNum = @intitNum + 1
end
end
end