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)
1 Like
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.