From: Manfred Koizar <mkoi-pg@aon.at>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: Christoph Haller <ch@rodos.fzk.de>
Cc: 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> (raw)
In-Reply-To: <200304172021.28073.mallah@trade-india.com>
References: <3E9EB59F.B01FBF60@rodos.fzk.de>
<200304172021.28073.mallah@trade-india.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mkoi-pg@aon.at, mallah@trade-india.com, ch@rodos.fzk.de
Subject: Re: getting rid of "Adding missing FROM-clause entry...."
In-Reply-To: <dbht9vctbp8p6cmt5jt8rvaqb8tf34p2ci@4ax.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox