如何复制某些行时，满足某些条件，然后做更新在PostgreSQL的某些列值?

Question

假设我有一个飞行记录数据样本，其中'type'列值为1或2(1表示单程旅行，2表示往返旅行):

customer_id, origin, destination, type
1111, London, Munchen, 2
2222, Rome, Paris, 1

我想复制某些记录，"类型"值为2，然后交换重复记录的"起源"one_answers"目的地"列的值，并将"类型"列值更改为1。

期望的输出是这样的:

customer_id, origin, destination, type
1111, London, Munchen, 1
1111, Munchen, London, 1
2222, Rome, Paris, 1

任何帮助都将是感激的。谢谢。

Answer 1

try this

WITH update_list AS
(
UPDATE your_table
SET type = 1
WHERE type = 2
RETURNING customer_id, origin, destination
)
INSERT INTO your_table (customer_id, origin, destination, type)
SELECT l.customer_id, l.destination, l.origin, 1
FROM update_list AS l