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

create table #t(id int,c char(1))

insert into #t values (1,'a'),(2,'b')

update #t set id= case when id=1 then 2
                       when id=2 then 1 end 

Hi,

First of all thanks for the reply. The solution you gave does not work for me. Actually my requirement is that except ID column in my table all the other multiple values should be swapped. could you please provide a query that works for my question. Actually iam a newbie to Sql server and i apreciate your detailed help on the same.

Thanks,

Ram

There is an ambiguity in your question. If you have many rows in your table, how do you identify which rows need to be taken for swapping?

You may explain your requirement to help you better.

Hi Latheesh,

I always have only two rows in my table, so that is not a problem at all. Could you please let me know the query that works for the same.

Thanks

Actually my requirement is that except ID column in my table all the other multiple values should be swapped.

Hi coolram4u,

Except ID column all the other multiple values should be swapped, can't we if the id got swapped, there's no need to swap other values? Uri's solution should work. As you mentioned, there're always only 2 rows in your table, to make it more specific in your case, please see below.

CREATE TABLE YourTable
(
ID INT,
PlateNo INT,
[Type] VARCHAR(20),
[Image Name] VARCHAR(20)
);

INSERT INTO YourTable
VALUES
(27,455,'User','img1.jpg'),
(32,542,'Alternative','img2.jpg');

;WITH Cte AS
(SELECT T.*,T2.ID AS ID2 FROM YourTable T JOIN YourTable T2 ON T.ID<>T2.ID)
UPDATE Cte SET ID=ID2

SELECT * FROM YourTable

DROP TABLE YourTable

If you have any question, feel free to let me

Hi Eric,

I can not swap the ID column in my requirement table as ID column is the primary key. So i definitely need to swap the other values in the table. Kindly guide me on the same.

Thanks,

Ram

Hi coolram4u,

Try this way.

CREATE TABLE YourTable
(
ID INT,
PlateNo INT,
[Type] VARCHAR(20),
[Image Name] VARCHAR(20)
);

INSERT INTO YourTable
VALUES
(27,455,'User','img1.jpg'),
(32,542,'Alternative','img2.jpg');

;WITH Cte AS
(SELECT T.*,T2.PlateNo PlateNo2, T2.Type Type2, T2.[Image Name] [Image Name 2] FROM YourTable T JOIN YourTable T2 ON T.ID<>T2.ID)
UPDATE Cte SET PlateNo = PlateNo2, Type=Type2,[Image Name]=[Image Name 2]


SELECT * FROM YourTable

DROP TABLE YourTable