Re: getting rid of "Adding missing FROM-clause entry...." - Mailing list pgsql-sql
From | Rajesh Kumar Mallah |
---|---|
Subject | Re: getting rid of "Adding missing FROM-clause entry...." |
Date | |
Msg-id | 200304171915.43852.mallah@trade-india.com Whole thread Raw |
In response to | Re: getting rid of "Adding missing FROM-clause entry...." (Christoph Haller <ch@rodos.fzk.de>) |
List | 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.