Re: Swappng Filds

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