CODEWITHSUNDEEP
sqlsql-server
Code_Geass
Code_Geass

Reputation: 79

Swap data from the same table

I need some help with a query that allows me to swap data in the same column like this:

ID    Name     Notebook Sales  Pen Sales
 1     33             2             3
 2     33             5             6
 3     5              8             9
 4     7              2             3
 5     35             5             6
 6     35             8             9

ID    Name     Notebook Sales  Pen Sales
 1     35             2             3
 2     35             5             6
 3     5              8             9
 4     7              2             3
 5     33             5             6
 6     33             8             9

In this example i swapped the name '33' for the name '35' keeping the original info of each one.

I'm working with 5000+ rows so doing something one by one gonna take a while.

The only way i thougth was switching the ones with the name '33' to '35' but then im gonna end with something like this:

ID    Name     Notebook Sales  Pen Sales
 1     35             2             3
 2     35             5             6
 3     5              8             9
 4     7              2             3
 5     35             5             6
 6     35             8             9

and then they are going to mix making dificult to change the first '35' ones to '33'.

Hope you can help me out with this and thanks in advance.

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You can do this in a single update (or select statement):

update t
    set name = (case when name = 33 then 35 else 33 end)
    where name in (33, 35);

In an update, the changes are not visible until they are statement ends and the changes are committed to the database. As a result, the values on the right-hand side of the assignment are from the "old" version of the table.

Upvotes: 2

Related Questions