Обсуждение: Swappng Filds

Hello-
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?

Thanks,
--greg

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

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

> Hello-
> 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?
>
> Thanks,
> --greg

this might be way off base, but why not leave the data
and swap the column names?

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com