SQLTeam.com | Weblogs | Forums

How to swap the values of two rows in a table


#1

Hi All,

Iam a newbie to Sql server and i am having a task where my current table with always two rows existing and ID column as primary key looks like below:

ID PlateNo Type Image Name
27 455 User img1.jpg
32 542 Alternative img2.jpg
And i want a sql query to modify my table so that the data should be like as shown below:

ID PlateNo Type Image Name
27 542 Alternative img2.jpg
32 455 User img1.jpg
Kindly help me on the same.

Thanks,

Ram

ram


#2

Is [ID] an identity column ?


#3

What is the purpose of the query? it seems more that the data is incorrect and you wish to update it?


#4

Yes ID is an identity column


#5

update MyTable set ID = case ID when 27 then 32 when 32 then 27 end where ID in (27, 32)


#6
UPDATE t1
SET PlateNo = t2.PlateNo, Type = t2.Type, [Image Name] = t2.[Image Name]
FROM table_name t1
INNER JOIN table_name t2 ON
    (t2.ID = 27 AND t1.ID = 32) OR
    (t2.ID = 32 AND t1.ID = 27)

#7

That will not work as ID is an identity columnn


#8

I would first make a copy of the table.

Then I would update the other columns (if you only have the three) as desired

UPDATE MyTable
SET PlateNo = '', Type = '', ImageName = ''
WHERE ID = 27

UPDATE MyTable
SET PlateNo = '', Type = '', ImageName = ''
WHERE ID = 32

Sorry for the original post as I hit the wrong key strokes while typing


#9

Might cause a problem if there was a UNIQUE index on one of the columns being swapped-over?


#10

My first shot was going to be delete the rows and replace them. Thought that might be a little drastic, but then again...


#11

No Foreign Keys then? :smiley:


#12

You can't update an identity column. Easiest is to just reassign the other column values, as in the UPDATE statement I posted earlier.


#13

I agree. I didn't take identity into account.