Reputation: 1095
I just got this question from my friend. I have one table as given below
DECLARE @table TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
NAME VARCHAR(30),
SURNAME VARCHAR(30)
)
INSERT INTO @table(NAME, SURNAME)
VALUES('Mohit', 'Vaghadiya'),
('Paresh', 'Panchal'),
('Devid','Pietersen'),
('kapil','Soni')
===================================
| ID | NAME | SURNAME |
=================================
| 1 | Mohit | Vaghadiya |
| 2 | Paresh | Panchal |
| 3 | Devid | Pietersen |
| 4 | kapil | Soni |
===================================
I just want to update NAME column value of ID '1' with SURNAME of ID '3' and vice versa I want to update SURNAME column value of ID '3' with the NAME of ID '1'
output will be
===================================
| ID | NAME | SURNAME |
===================================
| 1 | Pietersen | Vaghadiya |
| 2 | Paresh | Panchal |
| 3 | Devid | Mohit |
| 4 | kapil | Soni |
===================================
I know we can do it like this
DECLARE @name VARCHAR(30)
SELECT @name = SURNAME FROM @table WHERE ID = 3
UPDATE @table
SET SURNAME = (SELECT NAME FROM @table WHERE ID = 1)
WHERE ID = 3
UPDATE @table
SET NAME = @name
WHERE ID = 1
But is there any possible way to do it by executing only one UPDATE statement?
Upvotes: 2
Views: 2346
Reputation: 1870
UPDATE @table
SET SURNAME = CASE
WHEN ID = 3 THEN (SELECT NAME
FROM @table
WHERE ID = 1)
ELSE SURNAME
END,
NAME = CASE
WHEN ID = 1 THEN (SELECT SURNAME
FROM @table
WHERE ID = 3)
ELSE NAME
END
WHERE id IN ( 1, 3 )
Upvotes: 1
Reputation: 20509
This should work for you:
DECLARE @name VARCHAR(30)
SELECT @name = SURNAME FROM @table WHERE ID = 3
UPDATE @table
SET SURNAME = CASE
WHEN ID = 3
THEN (SELECT NAME FROM @table WHERE ID = 1)
ELSE SURNAME
END
,NAME = CASE
WHEN ID = 1
THEN @name
ELSE NAME
END
WHERE id IN (1,3)
Just use a CASE
statement to handle these two situations and update the SURNAME
column first and then the NAME
column.
Upvotes: 1
Reputation: 700910
You can join the table against itself with a condition that matches row 1 in the first set of the table with row 3 in the other. You can use the fact that x+y-x = y
and x+y-y = x
, i.e. the expression (3+1) - ID
calculates the other ID, which gives t1.ID = 4 - t2.ID
.
Then you need a condition on the values that you update, so that you only change the right one in each record:
update t1
set
NAME = case t1.ID when 1 then t2.SURNAME else t1.NAME end,
SURNAME = case t1.ID when 3 then t2.NAME else t1.SURNAME end
from
MyTable t1
inner join MyTable t2 on t2.ID = 4 - t1.ID
where
t1.ID in (1,3)
Demo: http://sqlfiddle.com/#!6/83257/11
Upvotes: 1
Reputation: 15329
You can use two case expressions, one for the name, other for the surname:
UPDATE @table
SET name =
case
when ID = 1 then (SELECT SURNAME FROM @table WHERE ID = 3)
else name
end,
surname =
case
when ID = 3 then (SELECT NAME FROM @table WHERE ID = 1)
else surname
end
WHERE ID in (1, 3)
Upvotes: 3