From: Manfred Koizar <mkoi-pg@aon.at>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: Christoph Haller <ch@rodos.fzk.de>, pgsql-sql@postgresql.org
Subject: Re: getting rid of "Adding missing FROM-clause entry...."
Date: Thu, 17 Apr 2003 17:30:06 +0200
Message-ID: <dbht9vctbp8p6cmt5jt8rvaqb8tf34p2ci@4ax.com>
References: <3E9EB59F.B01FBF60@rodos.fzk.de>
	<200304172021.28073.mallah@trade-india.com>
In-Reply-To: <200304172021.28073.mallah@trade-india.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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