Thread: Correlated subquery/update

Hi all-

I'm trying to match up two tables based on a primary key, and then copy
(update) a field in the second table to match a field in the first. Hard to
explain well... so here's an example:

select * from one;

a  | b
---------
1  | null
2  | null


select * from two;

c  | d
---------
1  | one
2  | two


In essence, I want to match up the records where one.a=two.c and update
one.b with the value in two.d . In Oracle, I would use this statement:

update one set b = (select d from two where one.a = two.c);

in psql, I get a syntax error when I do this. Either a correlated
subquery/update is not supported, or (more likely) I'm using the wrong
approach. Can anyone tell me whether this is supported, or how I can get the
job done using a different approach?

Thanks!
-Nick

---------------------------------------------------------------------
Nick Fankhauser

Business:
nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.  Software Consulting Services  http://www.ontko.com/

Personal:
nickf@fankhausers.com   http://www.fankhausers.com

"Nick Fankhauser" <nickf@ontko.com> writes:
> In essence, I want to match up the records where one.a=two.c and update
> one.b with the value in two.d . In Oracle, I would use this statement:
> update one set b = (select d from two where one.a = two.c);
> in psql, I get a syntax error when I do this.

play=> create table one (a int, b text);
CREATE
play=> create table two (c int, d text);
CREATE
play=> update one set b = (select d from two where one.a = two.c);
UPDATE 0

Looks OK to me (at least in 7.0.* and 7.1).  What release are you using?

            regards, tom lane

Sounds like the answer is to upgrade to v7+

Much thanks to Tom Lane and Len Morgan for the helpful responses!

-Nick

>update one set b = (select d from two where one.a = two.c);