Re: getting rid of "Adding missing FROM-clause entry...."

On Thu, 17 Apr 2003 20:21:28 +0530, Rajesh Kumar Mallah
<mallah(at)trade-india(dot)com> wrote:
>actually my orignal problem is to update
>10 *columns* in profile master first five comes
>from first entry in another table

This part is easy (using table and column names from your prototype):

UPDATE t_a
SET fname1=foo.fname , mname1=foo.mname
FROM (SELECT DISTINCT ON (id) id, fname, mname
FROM t_b
ORDER BY id, con_id
) AS foo
WHERE t_a.id = foo.id;

> and remaining
>5 columns comes from the second corresponding
>row in the other table.

This is a bit harder, because while DISTINCT ON (id) can be viewed as
sort of LIMIT 1 OFFSET 0 for each group of equal ids, there is no such
construct to select the *second* row of each group. So we build a
subquery that does not contain the first row of each group and take
the first row of the rest, i.e. the second row of the original group:

UPDATE t_a
SET fname2=foo.fname , mname2=foo.mname
FROM (SELECT DISTINCT ON (b2.id) b2.id, b2.fname, b2.mname
FROM t_b AS b1, t_b AS b2
WHERE b1.id = b2.id AND b1.con_id < b2.con_id
ORDER BY b2.id, b2.con_id) AS foo
WHERE t_a.id = foo.id;

Note that this does not set xname2 to NULL where no second
corresponding row exists. You might need a third UPDATE statement to
do this.

Servus
Manfred