CODEWITHSUNDEEP
sql-server
eavom
eavom

Reputation: 1095

How to update two column value vice versa using one UPDATE statement

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

Answers (4)

Mike Clark
Mike Clark

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

Radu Gheorghiu
Radu Gheorghiu

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

Guffa
Guffa

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

Jose Rui Santos
Jose Rui Santos

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)

sqlFiddle

Upvotes: 3

Related Questions