Re: ERROR: More than one tuple returned by a subselect used as an expression.

Thanks a lot Josh!!

I wasn't thinking too hard..

but then again the 2nd option (UPDATE..SET..FROM) you gave is really
something new to me. =)

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Josh Berkus
Sent: Friday, April 04, 2003 12:45 AM
To: mel@GMANMI.TV; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] ERROR: More than one tuple returned by a subselect
used as an expression.


Mel,

> query is equivalent to "update table1 set field6 = (select table2_field2
> from table2 where table2_field5 = table1.field5)"
> my question is, how do i reformulate my SQL so that i can update table1
> such that it only gets the first occurrence of table2_field5 on table2 and
> ignore all the other occurrences?  is there even a way where only 1 SQL
> statement is sufficient to carry out the desired result(s)?

There are a couple of ways.  What do you mean by "first occurance"?  First
chronologically, in primary key order, alphabetical, or something else?

UPDATE table1 SET field6 = (SELECT table2_field2
        FROM table2 WHERE table2_field5 = table1.field5
        ORDER BY table2_field9 LIMIT 1);

Or:

UPDATE table1 SET field6 = field2_min
FROM (SELECT field5, min(field2) as field2_min
        FROM table2 GROUP BY field5) t2
WHERE t2.field5 = table1.field5;

Which is better depends on the orginization of your data/tables as well as
what you mean by "first".

--
Josh Berkus
Aglio Database Solutions
San Francisco


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org