Update table

[WaveOut]

I want to write a query that will replace the contents of a column with 1 thru 162 until it replaces them all.

I have something like this:

UPDATE Craft_temp
SET craftID = id
WHERE id < 163

This replaces the first 162 items ... now I need to do the same thing 6 more times for items 163-324 and 325-487 etc ...

Dave

 

[SQLDenis]

UPDATE Craft_temp
SET craftID = id
WHERE id between 163 and 324

UPDATE Craft_temp
SET craftID = id
WHERE id between 325 and 487

etc etc

or in 1 shot
UPDATE Craft_temp
SET craftID = id

why do you need to do it in sets of 162?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[WaveOut]

SQLDenis,
Thanks for the reply. I've tried what you suggested before but it did not do what I was wanting. I need row 325 to have craftID of 1. Each time the value increases greater than 162 I need to to reset to 1 again.

Dave

 

[SQLDenis]

like this??

UPDATE Craft_temp
SET craftID = id-162
WHERE id between 325 and 488

UPDATE Craft_temp
SET craftID = id-324
WHERE id between 489 and 650

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[WaveOut]

Yep! Just like that!!!

Thanks!

 

[gmmastros]

UPDATE Craft_temp
Set CraftId = Case When id % 162 = 0
Then 162
Else id % 162
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom