Swap columns with a condition in SQL (Oracle)-CodePudding

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 ? (In Oracle)

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

CodePudding user response：

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;

CodePudding user response：

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)