CODEWITHSUNDEEP
sql-servert-sql
Rod
Rod

Reputation: 15477

updating column in db table

I have an int id column that I need to do a one-time maintenance/fix update on.

For example, currently the values look like this:
1
2
3

I need to make the following change:
1=3
2=1
3=2

Is there a way to do this in one statement? I keep imagining that the update will get confused if say the change from 1=3 to occurs then when it comes to 3=2 it will change that 1=3 update to 3=2 which gives

Incorrectly:
2
1
2

If that makes sense, rod.

Upvotes: 1

Views: 66

Answers (4)

Saggio
Saggio

Reputation: 2274

What about a sql case statement (something like this)?

UPDATE table SET intID = CASE 
WHEN intID = 3 THEN 2
WHEN intID = 1 THEN 3
WHEN intID = 2 THEN 1
END

Upvotes: 1

Conrad Frix
Conrad Frix

Reputation: 52675

This is how I usually do it

DECLARE @Update Table (@oldvalue int, @newvalue int)

INSERT INTO @Update  Values (1,3)
INSERT INTO @Update  Values (2,1)
INSERT INTO @Update  Values (3,2)

Update Table
SET 
   yourField = NewValue
FROM  
   table t
   INNER JOIN @Update 
   on t.yourField = @update.oldvalue

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

All of the assignments within an UPDATE statement (both the assignments within the SET clause, and the assignments on individual rows) are made as if they all occurred simultaneously.

So

UPDATE Table Set ID = ((ID + 1) % 3) + 1

(or whatever the right logic is, since I can't work out what "direction" is needed from the second table) would act correctly.

You can even use this knowledge to swap the value of two columns:

UPDATE Table SET a=b,b=a

will swap the contents of the columns, rather than (as you might expect) end up with both columns set to the same value.

Upvotes: 2

Randy Minder
Randy Minder

Reputation: 48522

In the past, I've done stuff like this by creating a temp table (whose structure is the same as the target table) with an extra column to hold the new value. Once I have all the new values, I'll then copy them to the target column in the target table, and delete the temp table.

Upvotes: 0

Related Questions