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 18:52:21 +0200
Message-ID: <nglt9vsctac48010936fbs2k6rmo8f1202@4ax.com>
References: <3E9EB59F.B01FBF60@rodos.fzk.de>
	<200304172021.28073.mallah@trade-india.com>
	<dbht9vctbp8p6cmt5jt8rvaqb8tf34p2ci@4ax.com>
	<200304172122.17761.mallah@trade-india.com>
In-Reply-To: <200304172122.17761.mallah@trade-india.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

On Thu, 17 Apr 2003 21:22:17 +0530, Rajesh Kumar Mallah
<mallah@trade-india.com> wrote:
>i think two of your queries will work for me.

I hope so, but ...

>I wrote:
>> 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;

... this can be slow, if you have large groups of equal id, because
each group blows up to n^2/2 rows.  You might be better off with a
subselect like

	SELECT DISTINCT ON (b2.id) b2.id, b2.fname, b2.mname
	  FROM t_b AS b2
	 WHERE EXISTS (SELECT *
	               FROM t_b AS b1
	               WHERE b1.id = b2.id AND b1.con_id < b2.con_id)
	 ORDER BY b2.id, b2.con_id

or

	SELECT b2.id, b2.fname, b2.mname
	  FROM t_b AS b2
	 WHERE (SELECT count(*)
	          FROM t_b AS b1
	         WHERE b1.id = b2.id AND b1.con_id < b2.con_id) = 1

The latter having the advantage of being standard SQL.  You have to
experiment a little to find out what works best for you.

BTW, my proposed UPDATE statements don't handle this case very well:

 id | con_id | fname | mname
----+--------+-------+-------
  1 |      1 | first | ...
  1 |      1 | 2nd   | ...
  1 |      2 | 3rd   | ...
  1 |      2 | 4th   | ...

Servus
 Manfred