From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: Manfred Koizar <mkoi-pg@aon.at>
Subject: Re: getting rid of "Adding missing FROM-clause entry...."
Date: Thu, 17 Apr 2003 21:22:17 +0530
User-Agent: KMail/1.5
Cc: Christoph Haller <ch@rodos.fzk.de>, pgsql-sql@postgresql.org
References: <3E9EB59F.B01FBF60@rodos.fzk.de>
	<200304172021.28073.mallah@trade-india.com>
	<dbht9vctbp8p6cmt5jt8rvaqb8tf34p2ci@4ax.com>
In-Reply-To: <dbht9vctbp8p6cmt5jt8rvaqb8tf34p2ci@4ax.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304172122.17761.mallah@trade-india.com>


Thanks Manfred,

i think two of your queries will work for me.
but i need to take a closer look.

BTW any idea why the query which i posted is not
working? and issuing a NOTICE.

regds
mallah.

On Thursday 17 Apr 2003 9:00 pm, Manfred Koizar wrote:
> 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

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.