Swapping column values in PostgreSQL

user_8007713 0
Viewed 5044

In my PostgreSQL database, I have a table with two text values, t1 and t2:

|   id   |   t1   |   t2   |  
|   1    |  abcd  |   xyz  |  
|   2    |  aazz  |   rst  |  
|   3    |  fgh   | qwerty |

I would like to swap the values of the columns t1 and t2 for every row in the table in a way that, using the above example, this would be the result:

|   id   |   t1   |   t2   |
|   1    |  xyz   |   abcd |
|   2    |  rst   |   aazz |
|   3    | qwerty |   fgh  |

Also, let's suppose the values from all rows with id=4 onwards (4, 5, 6...) are already correct, is it possible to filter which rows I want to swap?
I've tried this (for MySQL Databases) but none of the solutions worked.

sql-update postgresql
2 Answers

That's a simple UPDATE:

update the_table
  set t1 = t2, 
      t2 = t1
where id < 4;

Unlike MySQL, Postgres does this correctly.

user_3303150 
select * from swapit;
 id |  t1   |   t2
----+-------+--------
  1 | abcd  | xyz
  2 | aazz  | rst
  3 | fgh   | qwerty
  4 | first | second
  5 | first | second
(5 rows)

update swapit set t1 = t2, t2 = t1 where id <= 3;
UPDATE 3

select * from swapit order by id;
 id |   t1   |   t2   
----+--------+--------
  1 | xyz    | abcd
  2 | rst    | aazz
  3 | qwerty | fgh
  4 | first  | second
  5 | first  | second
(5 rows)
user_138083190
Related