Loop vid update Sql2005

yoggiMedlem sedan juni 20047 inlägg

Frågan4 aug. 2008 19:38#1

Skall köra denna rutin mot en tabell men kan man använda loop vid en update i sql uttrycket istället för köra 100,000 rader hehe.

<code>
UPDATE [CosmosVenture].[dbo].[CV_Object] SET [CV_Photo] = 'images/objects/1/main/1.jpg’ WHERE [AH_ObjectID] = ‘1'
UPDATE [CosmosVenture].[dbo].[CV_Object] SET [CV_Photo] = 'images/objects/XXX/main/xxx.jpg’ WHERE [AH_ObjectID] = 'xxx'
UPDATE [CosmosVenture].[dbo].[CV_Object] SET [CV_Photo] = 'images/objects/100000/main/100000.jpg’ WHERE [AH_ObjectID] ='‘100000'
</code>

LarsGMedlem sedan dec. 200012 464 inlägg

5 aug. 2008 09:51#2

declare @item int
declare @name varchar(10)
set @item = 1
while @item <= 100000
begin
      set @name = cast(@item as varchar(10))
      UPDATE [CosmosVenture].[dbo].[CV_Object] 
           SET [CV_Photo] = 'images/objects/' + @name + '/main/' + @name + '.jpg’ 
       WHERE [AH_ObjectID] = @item
      set @item = @item + 1
end