title: How to update two column value vice versa using one UPDATE statement
body: <p>I just got this question from my friend.
  I have one table as given below</p>
  
  <pre><code>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      |
  ===================================
  </code></pre>
  
  <p>I just want to update <strong>NAME</strong> column value of <strong>ID</strong> '1' with <strong>SURNAME</strong> of <strong>ID</strong> '3' and vice versa I want to update <strong>SURNAME</strong> column value of <strong>ID</strong> '3' with the <strong>NAME</strong> of <strong>ID</strong> '1'</p>
  
  <p>output will be</p>
  
  <pre><code>===================================
  | ID  |     NAME    |   SURNAME   |    
  ===================================
  | 1   |   Pietersen |   Vaghadiya |
  | 2   |   Paresh    |   Panchal   |
  | 3   |   Devid     |   Mohit     |
  | 4   |   kapil     |   Soni      |
  ===================================
  </code></pre>
  
  <p>I know we can do it like this</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But is there any possible way to do it by executing only one <strong>UPDATE</strong> statement?</p>