Thread: update syntax

update syntax

From
chester c young
Date:
how in pgsql do you do Oracle's

update t1 set ( c2, c3 ) =
( select c2, c3 from t1 where t1.c1 = t2.c1 );

thanks
chester

__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com


Re: update syntax

From
Bruce Momjian
Date:
chester c young wrote:
> how in pgsql do you do Oracle's
> 
> update t1 set ( c2, c3 ) =
> ( select c2, c3 from t1 where t1.c1 = t2.c1 );

Well, because we have FROM in UPDATE:
UPDATE t1 SET c2 = t2.c2, c3 = t2.c3FROM t1 t2WHERE t1.c1 = t2.c1;

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: update syntax

From
chester c young
Date:
thanks, but how would this be done for the same table?

update table1 t1 set (c2, c3 ) = 
( select c2, c3 from table1 t2 where t1.c1 = t2.c1 and t1.oid < t2.oid
);

and, when tables are listed in the from clause, are they comma
separated?




__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com


Re: update syntax

From
Bruce Momjian
Date:
chester c young wrote:
> thanks, but how would this be done for the same table?
> 
> update table1 t1 set (c2, c3 ) = 
> ( select c2, c3 from table1 t2 where t1.c1 = t2.c1 and t1.oid < t2.oid
> );

In that 'FROM c1 c2', c2 was a table alias for table c1.  That is how
you get the second instance of c1.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026