Обсуждение: getting rid of "Adding missing FROM-clause entry...."
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.
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 offset0) 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.
> > 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_idorder by contact_id ) as a WHERE source='CATALOG' ; Does this work? Regards, Christoph
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.
> > 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
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.idorder 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, mname2character 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.
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 fooWHERE 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 FROMt_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 fooWHERE 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. ServusManfred
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.
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. ServusManfred
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.idAND 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 | ... ServusManfred