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
khtan
September 10, 2015, 7:27am
2
Is [ID] an identity column ?
Dohsan
September 10, 2015, 8:00am
3
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)
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
khtan
September 11, 2015, 2:46am
7
That will not work as ID is an identity columnn
djj55
September 11, 2015, 10:51am
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
Kristen
September 11, 2015, 11:14am
9
Might cause a problem if there was a UNIQUE index on one of the columns being swapped-over?
djj55
September 11, 2015, 11:39am
10
My first shot was going to be delete the rows and replace them. Thought that might be a little drastic, but then again...
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.