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

Collapse
Posted by Roberto Mello on
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.

Collapse
Posted by Roberto Mello on
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.