pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting rid of "Adding missing FROM-clause entry...."
10+ messages / 3 participants
[nested] [flat]
* getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 10:27 Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-17 10:27 UTC (permalink / raw)
To: pgsql-sql
Hi I am having problem with an UPDATE ... FROM
SQL where the FROM table is a subquery.
UPDATE profile_master set title1=a.title1 FROM
(
SELECT company_id , titile as title1 ,fname as fname1 ,mname as mname1 ,lname as
lname1 ,company_position as desg1 from public.catalog_key_contacts where
company_id=profile_master.company_id order by contact_id limit 1 offset 0
) as a
where profile_master.source='CATALOG' ;
NOTICE: Adding missing FROM-clause entry in subquery for table "profile_master"
UPDATE 711
its showing an update of 711 however all the 711 matching rows are getting
updated to the same value.
can anyone tell me how to write this query properly ?
Regds
Mallah.
--
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 12:18 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-17 12:18 UTC (permalink / raw)
To: pgsql-sql; +Cc: daq <daq@ugyvitelszolgaltato.hu>
Actually i have to update multiple columns my original
query was:
UPDATE profile_master SET
title1=a.title1 , fname1=a.fname1 , mname1=a.mname1 ,
lname1=a.lname1 , desg1 = a.desg1 FROM
(
SELECT company_id , titile as title1 ,fname as fname1 ,mname as mname1 ,
lname as lname1 ,company_position as desg1 from public.catalog_key_contacts as b
where b.company_id=profile_master.company_id order by contact_id limit 1 offset 0)
as a WHERE
profile_master.source='CATALOG' ;
Regds
Mallah.
On Thursday 17 Apr 2003 3:57 pm, Rajesh Kumar Mallah wrote:
> Hi I am having problem with an UPDATE ... FROM
> SQL where the FROM table is a subquery.
>
> UPDATE profile_master set title1=a.title1 FROM
> (
>
> SELECT company_id , titile as title1 ,fname as fname1 ,mname as mname1
> ,lname as lname1 ,company_position as desg1 from
> public.catalog_key_contacts where company_id=profile_master.company_id
> order by contact_id limit 1 offset 0 ) as a
> where profile_master.source='CATALOG' ;
> NOTICE: Adding missing FROM-clause entry in subquery for table
> "profile_master" UPDATE 711
>
> its showing an update of 711 however all the 711 matching rows are getting
> updated to the same value.
>
> can anyone tell me how to write this query properly ?
>
> Regds
> Mallah.
--
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 13:20 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 10+ messages in thread
From: Christoph Haller @ 2003-04-17 13:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: mallah@trade-india.com
>
> Actually i have to update multiple columns my original
> query was:
>
> UPDATE profile_master SET
>
> title1=a.title1 , fname1=a.fname1 , mname1=a.mname1 ,
> lname1=a.lname1 , desg1 = a.desg1 FROM
>
> (
> SELECT company_id , titile as title1 ,fname as fname1 ,mname as
mname1 ,
> lname as lname1 ,company_position as desg1 from
public.catalog_key_contacts as b
> where b.company_id=profile_master.company_id order by contact_id
limit 1 offset 0)
>
> as a WHERE
>
> profile_master.source='CATALOG' ;
>
> > Hi I am having problem with an UPDATE ... FROM
> > SQL where the FROM table is a subquery.
> >
> > UPDATE profile_master set title1=a.title1 FROM
> > (
> >
> > SELECT company_id , titile as title1 ,fname as fname1 ,mname as
mname1
> > ,lname as lname1 ,company_position as desg1 from
> > public.catalog_key_contacts where
company_id=profile_master.company_id
> > order by contact_id limit 1 offset 0 ) as a
> > where profile_master.source='CATALOG' ;
> > NOTICE: Adding missing FROM-clause entry in subquery for table
> > "profile_master" UPDATE 711
> >
> > its showing an update of 711 however all the 711 matching rows are
getting
> > updated to the same value.
> >
> > can anyone tell me how to write this query properly ?
> >
I think you need a second reference to the profile_master table.
And why are you using "limit 1 offset 0"?
So I'd say
UPDATE profile_master SET
title1=a.title1 , fname1=a.fname1 , mname1=a.mname1 ,
lname1=a.lname1 , desg1 = a.desg1 FROM
(
SELECT company_id , title as title1 ,fname as fname1 ,mname as mname1
,
lname as lname1 ,company_position as desg1
from public.catalog_key_contacts as b ,profile_master as c
where b.company_id=c.company_id order by contact_id )
as a WHERE
source='CATALOG' ;
Does this work?
Regards, Christoph
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 13:45 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-17 13:45 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Thursday 17 Apr 2003 6:50 pm, Christoph Haller wrote:
> > Actually i have to update multiple columns my original
> > query was:
> >
> > UPDATE profile_master SET
> >
> > title1=a.title1 , fname1=a.fname1 , mname1=a.mname1 ,
> > lname1=a.lname1 , desg1 = a.desg1 FROM
> >
> > (
> > SELECT company_id , titile as title1 ,fname as fname1 ,mname as
>
> mname1 ,
>
> > lname as lname1 ,company_position as desg1 from
>
> public.catalog_key_contacts as b
>
> > where b.company_id=profile_master.company_id order by contact_id
>
> limit 1 offset 0)
>
> > as a WHERE
> >
> > profile_master.source='CATALOG' ;
> >
> > > Hi I am having problem with an UPDATE ... FROM
> > > SQL where the FROM table is a subquery.
> > >
> > > UPDATE profile_master set title1=a.title1 FROM
> > > (
> > >
> > > SELECT company_id , titile as title1 ,fname as fname1 ,mname as
>
> mname1
>
> > > ,lname as lname1 ,company_position as desg1 from
> > > public.catalog_key_contacts where
>
> company_id=profile_master.company_id
>
> > > order by contact_id limit 1 offset 0 ) as a
> > > where profile_master.source='CATALOG' ;
> > > NOTICE: Adding missing FROM-clause entry in subquery for table
> > > "profile_master" UPDATE 711
> > >
> > > its showing an update of 711 however all the 711 matching rows are
>
> getting
>
> > > updated to the same value.
> > >
> > > can anyone tell me how to write this query properly ?
>
> I think you need a second reference to the profile_master table.
> And why are you using "limit 1 offset 0"?
becuase the subquery results in mutiple rowss.
I want the first row to update title1,fname1,mname1,lname1,degs1
and use the second row by using "limit 1 offset 1" to
update title2,fname2,mname2,lname2,degs2 etc
do u still want me to try the query u gave?
(actually i did try and its giving some trivial error)
regds
mallah.
> So I'd say
>
> UPDATE profile_master SET
>
> title1=a.title1 , fname1=a.fname1 , mname1=a.mname1 ,
> lname1=a.lname1 , desg1 = a.desg1 FROM
>
> (
> SELECT company_id , title as title1 ,fname as fname1 ,mname as mname1
> ,
> lname as lname1 ,company_position as desg1
> from public.catalog_key_contacts as b ,profile_master as c
> where b.company_id=c.company_id order by contact_id )
>
> as a WHERE
>
> source='CATALOG' ;
>
> Does this work?
> Regards, Christoph
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 14:09 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 10+ messages in thread
From: Christoph Haller @ 2003-04-17 14:09 UTC (permalink / raw)
To: pgsql-sql; mallah@trade-india.com
> > And why are you using "limit 1 offset 0"?
>
> becuase the subquery results in mutiple rowss.
> I want the first row to update title1,fname1,mname1,lname1,degs1
>
> and use the second row by using "limit 1 offset 1" to
> update title2,fname2,mname2,lname2,degs2 etc
>
> do u still want me to try the query u gave?
> (actually i did try and its giving some trivial error)
>
No. Looks like a misunderstanding.
Do I get it right this time assuming you want to
set up multiple update commands which update
only one row at a time?
Regards, Christoph
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 14:51 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-17 14:51 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Thursday 17 Apr 2003 7:39 pm, Christoph Haller wrote:
> > > And why are you using "limit 1 offset 0"?
> >
> > becuase the subquery results in mutiple rowss.
> > I want the first row to update title1,fname1,mname1,lname1,degs1
> >
> > and use the second row by using "limit 1 offset 1" to
> > update title2,fname2,mname2,lname2,degs2 etc
> >
> > do u still want me to try the query u gave?
> > (actually i did try and its giving some trivial error)
>
> No. Looks like a misunderstanding.
> Do I get it right this time assuming you want to
> set up multiple update commands which update
> only one row at a time?
> Regards, Christoph
Dear Chris ,
Thanks so much for ur interest.
I have prepared a prototype for this
problem and including the SQL.
actually my orignal problem is to update
10 *columns* in profile master first five comes
from first entry in another table and remaining
5 columns comes from the second corresponding
row in the other table. the two rows in the
second columns have the same company_id but
different contact_id. sort of denormalizing
excercise.
this needs to be done for all the companies
in 2 seperate update commands(or better 1).
Eg: below is a prototype for the problem.
(SQL are included below)
Table t_b
+----+--------+-------+-------+
| id | con_id | fname | mname |
+----+--------+-------+-------+
| 1 | 1 | X | Y |
| 1 | 2 | U | V |
| 2 | 1 | S | T |
+----+--------+-------+-------+
shud be translated to
+----+--------+--------+--------+--------+
| id | fname1 | mname1 | fname2 | mname2 |
+----+--------+--------+--------+--------+
| 1 | X | Y | U | V |
| 2 | S | T | NULL | NULL |
+----+--------+--------+--------+--------+
but
UPDATE t_a set fname1=foo.fname1 , mname1=foo.mname1 FROM
(select fname as fname1 , mname as mname1 from t_b where id=t_a.id
order by con_id limit 1 offset 0) as foo ;
gives:
NOTICE: Adding missing FROM-clause entry in subquery for table "t_a"
and updates wrongly.
tradein_clients=# SELECT * from t_a;
+----+--------+--------+--------+--------+
| id | fname1 | mname1 | fname2 | mname2 |
+----+--------+--------+--------+--------+
| 1 | X | Y | NULL | NULL |
| 2 | X | Y | NULL | NULL |
+----+--------+--------+--------+--------+
(2 rows)
// SQL BEGINS.
CREATE TABLE t_b (
id integer,
con_id integer,
fname character varying,
mname character varying
);
INSERT INTO t_b (id, con_id, fname, mname) VALUES (1, 1, 'X', 'Y');
INSERT INTO t_b (id, con_id, fname, mname) VALUES (1, 2, 'U', 'V');
INSERT INTO t_b (id, con_id, fname, mname) VALUES (2, 1, 'S', 'T');
CREATE TABLE t_a (
id integer,
fname1 character varying,
mname1 character varying,
fname2 character varying,
mname2 character varying
);
INSERT INTO t_a (id, fname1, mname1, fname2, mname2) VALUES (1, 'X', 'Y', NULL, NULL);
INSERT INTO t_a (id, fname1, mname1, fname2, mname2) VALUES (2, 'X', 'Y', NULL, NULL);
// SQL ENDS.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 15:30 Manfred Koizar <mkoi-pg@aon.at>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 10+ messages in thread
From: Manfred Koizar @ 2003-04-17 15:30 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 15:52 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 2 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-17 15:52 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 16:02 Manfred Koizar <mkoi-pg@aon.at>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 10+ messages in thread
From: Manfred Koizar @ 2003-04-17 16:02 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Thu, 17 Apr 2003 21:22:17 +0530, Rajesh Kumar Mallah
<mallah@trade-india.com> wrote:
>BTW any idea why the query which i posted is not
>working? and issuing a NOTICE.
I guess, in
UPDATE t1
SET ...
FROM (SELECT ... FROM t2
WHERE t2.id = t1.id);
the subselect is not correlated because t1 and the subquery are on the
same level. So the subquery is treated like a standalone query and as
it does not have t1 in its FROM clause, t1 is added automatically.
Then the result of the subquery is joined to each row of t1 for the
update. This also explains why all rows were updated to the same
values.
Servus
Manfred
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting rid of "Adding missing FROM-clause entry...."
@ 2003-04-17 16:52 Manfred Koizar <mkoi-pg@aon.at>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 10+ messages in thread
From: Manfred Koizar @ 2003-04-17 16:52 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2003-04-17 16:52 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-17 10:27 getting rid of "Adding missing FROM-clause entry...." Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-17 12:18 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-17 13:20 Re: getting rid of "Adding missing FROM-clause entry...." Christoph Haller <ch@rodos.fzk.de>
2003-04-17 13:45 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-17 14:09 Re: getting rid of "Adding missing FROM-clause entry...." Christoph Haller <ch@rodos.fzk.de>
2003-04-17 14:51 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-17 15:30 ` Manfred Koizar <mkoi-pg@aon.at>
2003-04-17 15:52 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-17 16:02 ` Manfred Koizar <mkoi-pg@aon.at>
2003-04-17 16:52 ` Manfred Koizar <mkoi-pg@aon.at>
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