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
