RE: [GENERAL] Correlated subquery/update
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);
Re: [GENERAL] Correlated subquery/update
"Nick Fankhauser" <[EMAIL PROTECTED]> 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
[GENERAL] 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: [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ Personal: [EMAIL PROTECTED] http://www.fankhausers.com
