Hello,

I need Postgres support for the following update syntax :

update table1 set (col1, col2, col3) = (select f1, f2, f3 from table2
where id=5) where rownum=3;

update table1 set (col1, col2, col3) = (select f1, f2, f3 from table2
where id=table1.parentid) where rownum=3;

These type of update statements are the only ones supported by Oracle
to update one table from another.

Can this be added to the TODO list?
Can a cash prize be associated with the TODO item?, I have $1000US
allocated for this.

-ESR-

30 Jan 2004 07:41:18 -0800
ramirez@idconcepts.org (Edwin S. Ramirez)
Update Syntax
On Fri, Jan 30, 2004 at 07:41:18 -0800,
  "Edwin S. Ramirez" <ramirez@idconcepts.org> wrote:
> Hello,
> 
> I need Postgres support for the following update syntax :
> 
> update table1 set (col1, col2, col3) = (select f1, f2, f3 from table2
> where id=5) where rownum=3;
> 
> update table1 set (col1, col2, col3) = (select f1, f2, f3 from table2
> where id=table1.parentid) where rownum=3;
> 
> These type of update statements are the only ones supported by Oracle
> to update one table from another.

As a work around you could do:
update table1 set
  col1 = (select f1 from table2 where id=table1.parentid),
  col2 = (select f2 from table2 where id=table1.parentid),
  col3 = (select f3 from table2 where id=table1.parentid)
  where rownum=3;

I don't think this will run as efficiently and it makes the query look
more complex, but it should work for both Oracle and Postgres.

Mon, 2 Feb 2004 11:49:04 -0600
Bruno Wolff III <bruno@wolff.to>
Re: Update Syntax