Re: getting rid of "Adding missing FROM-clause entry...." - Mailing list pgsql-sql

On Thu, 17 Apr 2003 20:21:28 +0530, Rajesh Kumar Mallah
<mallah@trade-india.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 fooWHERE 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
FROMt_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 fooWHERE
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.

ServusManfred