How do I swap column values in sql server 2008?

Question

I have a table called Employee

 Eno     ename     AttributeValue      AttributeName  
 1       aa           a123             abc
 2       bbb          b123             dcf
 3       cc           c7sd             wew3

I want to swap the data from column AttributeValue to AttributeName and AttributeName to AttributeValue

For Example:

Eno     ename     AttributeValue   AttributeName  
1       aa        abc              a123
2       bbb       dcf              b123
3       cc        wew3             c7sd

score 112 · Accepted Answer · edited Jul 02 '13 at 10:05

112

UPDATE employee
SET AttributeValue = AttributeName, 
    AttributeName = AttributeValue

However, unless both columns have the exact same definition, you risk losing information.

edited Jul 02 '13 at 10:05

Smi

13,850
9
56
64

answered Nov 16 '10 at 20:26

Oded

489,969
99
883
1,009

1

"unless both columns have the exact same definition, you risk loosing information." - just to be sure, are you saying unless the two columns are same datatype we could loose info? Or what do you mean by definition? thx – VoodooChild Apr 30 '13 at 17:29
@VoodooChild - data type, yes. Though that includes precision and size if relevant. – Oded May 01 '13 at 09:36
11

Can't believe it's that simple. Coming from a C++ mindset my brain kept saying "nah... that won't work. Both value's will end up being whatever the initial value of `AttributeName` was." – Drew Chapin Apr 02 '14 at 19:28
14

@druciferre It's declarative programming. Say what you want, not how to accomplish it. – Bacon Bits Apr 02 '14 at 19:34
The C++ approach was killing me, this is brilliant! So easy Wow ! – Jeancarlo Fontalvo Oct 09 '16 at 08:59
I looked it up and it's actually correct according to the SQL standard. The following is taken from the session about 'update statement: searched' of SQL-1992: 'the value expressions are effectively evaluated for each row of T before updating any row of T'. – LordCapybara Sep 14 '20 at 18:04

score 14 · Answer 2 · answered Nov 16 '10 at 20:26

14

Update employee
Set attributeValue = attributeName,
    attributeName = attributeValue

answered Nov 16 '10 at 20:26

John Hartsock

85,422
23
131
146

score 7 · Answer 3 · edited Jul 02 '13 at 10:06

7

update Employee set AttributeValue = AttributeName, AttributeName = AttributeValue

edited Jul 02 '13 at 10:06

Smi

13,850
9
56
64

answered Nov 16 '10 at 20:26

GendoIkari

11,734
6
62
104

bormat · Answer 4 · 2020-09-18T16:33:50.020

All the previous techniques are slow for big tables they move data instead of renaming columns, this is a simple solution:

ALTER TABLE "amplitude"
RENAME COLUMN "start_hour_displayed" TO "temp";

ALTER TABLE "amplitude"
RENAME COLUMN "start_hour" TO "start_hour_displayed";

ALTER TABLE "amplitude"
RENAME COLUMN "temp" TO "start_hour";

If you have views of functions linked you have to backup them before and restore them after.

score 3 · Answer 5 · answered Oct 07 '14 at 13:47

3

This is really good example

SELECT * from employees;
Go

DECLARE @temp as varchar(20)
update employees
set    @temp = fname,
       fname = lname,
       lname = @temp
WHERE  deptno = 10;
GO

SELECT * from employees;

Result

answered Oct 07 '14 at 13:47

Mind Peace

905
8
29

Can you explain the behavior? Does the database engine first assign `fname` to `@temp`, then assing `lname` to `fname`, then `@temp` to `lname`? If this is the case, how do you explain `set a=b, b=a`? – Gqqnbig Aug 20 '15 at 23:54
3

Why would you do this? You don't need a swap variable, check Oded's answer. – sventevit Apr 10 '16 at 18:10

score 1 · Answer 6 · edited Aug 15 '17 at 06:05

Declare @myTable Table (id int, first_name varchar(50), last_name varchar(50));

Select * from Student

Insert Into @myTable (id, first_name, last_name) Select id, last_name, first_name from Student

    MERGE
    INTO    Student std
    USING @myTable tmp
    ON std.id = tmp.id
    WHEN MATCHED THEN
    UPDATE
    SET std.first_name = tmp.first_name,
    std.last_name = tmp.last_name;

Select * from Student

Output

Query Result Screenshot

score 0 · Answer 7 · edited Jul 09 '17 at 11:58

0

Just swap both columns in a single update:

Update registration
Set AttributeName = AttributeValue ,
    AttributeValue = AttributeName where id in (1,2,3)

edited Jul 09 '17 at 11:58

answered Jul 09 '17 at 09:00

Dinesh Kumar Sharma

11
3

score 0 · Answer 8 · answered Aug 03 '18 at 10:05

UPDATE employee SET AttributeValue = AttributeName, AttributeName = AttributeValue its not correct better to create one temp column and try the code UPDATE employee SET temp= AttributeName then again UPDATE employee SET AttributeName = AttributeValuee again

UPDATE employee SET AttributeValuee= temp

How do I swap column values in sql server 2008?

8 Answers8

Linked

Related