Forum OpenACS Q&A: UPDATE with results of a query

Posted by Roberto Mello on 09/09/00 06:32 PM

And here's another one...

I am trying to do a sequential update in a PL/Tcl function. The PG Docs say that you can have an optional FROM clause in an UPDATE statement, but the following yields 0 updated rows:

lbn=# update foo set pkey = b.page_id
lbn-# from bar b
lbn-# where b.page_id in (select page_id from bar);
UPDATE 0

In short, I am trying to update all rows in a table with the results of a query. Is that possible? Here's what the docs say about the from clause:

[ FROM fromlist ]
fromlist
     A Postgres non-standard extension to allow columns from other
tables to appear in the WHERE condition.

Thanks again.

2: Response to UPDATE with results of a query (response to 1)

Posted by Roberto Mello on 09/11/00 06:48 AM

Ok, I didn't know, but I found out that this is ok:

update foo set pkey = (select page_id from bar b
	where b.a_field = another_table.a_field);

But this is not:

update foo set pkey = (select page_id from bar b,another_table at
	where b.a_field = at.a_field);

Something to do with more than one tuple being returned although both queries return the exact same thing to my eyes.