Xchange column values in table in SQL Server

Question

Hello people. I have problem to solve:

Suppose in database is table TAB1(id,numb,text). The task is to exchange two column values in column 'numb' on some query in column 'text'.

For example:

We have

ID | NUMB | TEXT
 1 |  22  | hello   <- this record  
 2 |  25  | today  
 3 |  34  | wow     <- this record  
 4 |  53  | what  

After query executed we must have

ID | NUMB | TEXT
 1 |  34  | hello   <- this record  
 2 |  25  | today  
 3 |  22  | wow     <- this record  
 4 |  53  | what  

To do this i wrote query:

UPDATE TAB1 AS A, TAB1 AS B SET A.numb=B.numb,B.numb=A.numb WHERE A.numb='hello' AND B.numb='wow';  

But this query don't want to execute - popups error 'Recipient B.numb repeated';

Please, give me some suggestions.

Answer 1

Typically this should not be attempted in a single query, but using SQL SERVER 2005 you can try

DECLARE @TableA TABLE(
        ID INT,
        NUMB INT,
        TEXT VARCHAR(20)
)
 INSERT INTO @TableA SELECT 1,22,'hello'
 INSERT INTO @TableA SELECT 2,25,'today'   
 INSERT INTO @TableA SELECT 3,34,'wow'
 INSERT INTO @TableA SELECT 4,53,'what' 

SELECT  *
FROM    @TableA

;WITH Vals1 AS(
        SELECT  *
        FROM    @TableA
        WHERE   TEXT = 'hello'
),
Vals2 AS(
        SELECT  *
        FROM    @TableA
        WHERE   TEXT = 'wow'
)
UPDATE  @TableA
SET     NUMB = CASE 
                    WHEN t.TEXT = Vals1.TEXT THEN Vals2.NUMB 
                    WHEN t.TEXT = Vals2.TEXT THEN Vals1.NUMB 
                    ELSE t.NUMB 
                END
FROM    @TableA t,
        Vals1,
        Vals2

SELECT  *
FROM    @TableA