Greg Lindstrom <greg.lindstrom@novasyshealth.com> writes:
> I have a table with values in two columns that I want to swap; that is,
> I want the value in column A to now be in column B and the value in
> column B to be in column A. I tried...
> UPDATE my_table SET A=B, B=A WHERE mycontition = True
> But that seemed to place the value of column B into A, then the new
> value of A into B, so both A and B contained value B. Is there a common
> technique to accomplish this?
Works for me:
postgres=# create table foo (a int, b int);
CREATE TABLE
postgres=# insert into foo values (11,22);
INSERT 0 1
postgres=# insert into foo values (33,55);
INSERT 0 1
postgres=# insert into foo values (99,77);
INSERT 0 1
postgres=# update foo set a=b, b=a where a < 99;
UPDATE 2
postgres=# select * from foo;
a | b
----+----
99 | 77
22 | 11
55 | 33
(3 rows)
I suspect your "seemed to" is glossing over some relevant points you
failed to bring out ...
regards, tom lane