CODEWITHSUNDEEP
sqloracle-database
startWithSQL
startWithSQL

Reputation: 11

Swap columns with a condition in SQL (Oracle)

I have a table with two columns- x and y. x should be always less than y in my output table.

How to swap? I tried update which didn't work and throws runtime error. Can someone give the correct syntax ?

Update table t1 set x = (case when x<y then x else y end),
                set y = (case when x<y then y else x end)

enter image description here

Upvotes: 0

Views: 202

Answers (2)

forpas
forpas

Reputation: 164089

Use a WHERE clause to update only the rows that actually need to be updated:

UPDATE tablename
SET x = y,
    y = x
WHERE x > y;

Upvotes: 4

Andrew Sayer
Andrew Sayer

Reputation: 2336

Your syntax is wrong. There should be no 'TABLE' in the UPDATE statement. You have SET twice, it's only wanted once

Update t1 set x = (case when x<y then x else y end),
 y = (case when x<y then y else x end)

Upvotes: 2

Related Questions