在 PostgreSQL 中交换列值 [英] Swapping column values in PostgreSQL

问题描述

在我的 PostgreSQL 数据库中，我有一个包含两个文本值的表，t1 和 t2:

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 |  

我想为表中的每一行交换列 t1 和 t2 的值，使用上面的例子，这将是结果:

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  |

另外，假设 id=4 之后的所有行(4、5、6...)的值都已经正确，是否可以过滤我想要交换的行?
我已经尝试过这个(对于 MySQL 数据库)，但没有一个解决方案有效.

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.

推荐答案

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)

这篇关于在 PostgreSQL 中交换列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！