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.
That's a simple UPDATE:
update the_table
set t1 = t2,
t2 = t1
where id < 4;
Unlike MySQL, Postgres does this correctly.
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)