11

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.

Guilherme Barboza
  • 567
  • 5
  • 22

2 Answers2

19

That's a simple UPDATE:

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

Unlike MySQL, Postgres does this correctly.

16
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)
Mike Organek
  • 11,647
  • 3
  • 11
  • 26
  • 4
    Everybody forgets about tasty PostgreSQL feature: `set (t1, t2) = (t2, t1)` – Abelisto Dec 06 '20 at 22:05
  • 1
    Worth mentioning that @abelisto 's comment is for the normal [`UPDATE`-statemtent](https://www.postgresql.org/docs/current/sql-update.html). I searched for a PL/PGSQL-statement `SET` for assigning multiple variables at once, which obviously doesn't exist. – taffit Jul 05 '21 at 12:09
  • @taffit `select t1, t2 into t2, t1;` If you mean C-style `a = b = 1;` then yes, pl/sql based on Pascal/Ada syntax where the assignment statement does not returns value. – Abelisto Jul 05 '21 at 15:59
  • @abelisto Correct, thanks for the clarification. Indeed I checked for a PL/PGSQL `SET`-command, which doesn't exist. Using `SELECT ... INTO ...` helps with the swap, while the syntax you mentioned works perfectly on an `UPDATE ... SET ...`-statement. – taffit Jul 05 '21 at 19:37