Swap multiple column values in postgres [duplicate]

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. — user330315
– user330315, Commented Aug 27, 2020 at 8:38

GMB · Accepted Answer · 2020-08-27 08:35:31Z

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, 2020 at 8:35

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1