From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | Rajesh Kumar Mallah <mallah(at)trade-india(dot)com> |
Cc: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: getting rid of "Adding missing FROM-clause entry...." |
Date: | 2003-04-17 16:52:21 |
Message-ID: | nglt9vsctac48010936fbs2k6rmo8f1202@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, 17 Apr 2003 21:22:17 +0530, Rajesh Kumar Mallah
<mallah(at)trade-india(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Randall Lucas | 2003-04-17 18:01:25 | Ordinal value of row within set returned by a query? |
Previous Message | Tom Lane | 2003-04-17 16:10:32 | Re: planner question.. |