SQLTeam.com | Weblogs | Forums

How to swap the values of two rows in a table


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.





Is [ID] an identity column ?


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


Yes ID is an identity column


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

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)


That will not work as ID is an identity columnn


I would first make a copy of the table.

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

SET PlateNo = '', Type = '', ImageName = ''

SET PlateNo = '', Type = '', ImageName = ''

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


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


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


No Foreign Keys then? :smiley:


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


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