Re: 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