Swap multiple column values in postgres

Question

I am trying to swap multiple column values that were incorrectly swapped due to backend code. I am trying to use the following:

UPDATE insured
SET address1=(:risk_address1_var:=address1), address1 = risk_address1, risk_address1 = :risk_address1_var;

It's not working like it works for mysql as temp variable(using @). I want to execute this sql query in the liquibase migration code.

That hack is not needed in Postgres - it properly supports just swapping them in place. The variable is essentially a workaround for a MySQL bug. — , Aug 27 '20 at 08:38

score 1 · Answer 1 · answered Aug 27 '20 at 08:35

1

No need for a temporary variable. Unlike MySQL, Postgres allows doing what you want with simply:

UPDATE insured
SET address1 = risk_address1, risk_address1 = address1;

answered Aug 27 '20 at 08:35

GMB

1 Answers1