Re: Swappng Filds

On Wed, Jan 18, 2006 at 02:52:45PM -0600, Greg Lindstrom wrote:
> 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?

What version of PostgreSQL are you running?  It works for me in the
CVS versions of 7.3 and later:

test=> CREATE TABLE foo (a text, b text);
CREATE TABLE
test=> INSERT INTO foo VALUES ('a1', 'b1');
INSERT 0 1
test=> INSERT INTO foo VALUES ('a2', 'b2');
INSERT 0 1
test=> SELECT * FROM foo;
 a  | b
----+----
 a1 | b1
 a2 | b2
(2 rows)

test=> UPDATE foo SET a = b, b = a;
UPDATE 2
test=> SELECT * FROM foo;
 a  | b
----+----
 b1 | a1
 b2 | a2
(2 rows)

Can you post a complete test case that shows different behavior?

--
Michael Fuhr