RE: Update Statement
The EXISTS causes a hash join, the in causes a 'nested loop' type behaviour. Use what is optimal for you. cheers j. > -----Original Message----- > From: Stefan Weideneder [mailto:stefan@weideneder.de] > Sent: Thursday, February 15, 2001 3:19 PM > To: informix-list@iiug.org > Subject: Re: Update Statement > > > Hi, > > how about this ? > > update admin.subc set admin.subc.deal_key = "NVD" > where exists ( select 1 from dblog1.eire where > dblog1.eire.chas = admin.subc.chas_key ); > > The statement might be faster if you would > use the select-statement in an ordinary > sub-query: > > where admin.subc.chas_key in ( select dblog1.eire.chas > from dblog1.eire ); > > But I'm afraid that the select would return too much > rows. > > Regards > > Stefan > > Kirk Davis wrote: > > > > Hi All, > > > > Hope someone out there can help me with this one, it's > driving me mad! > > > > Does anyone have any idea, why the statement below would > not work, I just > > get the error " 522: Table (dblog1.eire) not selected in query." > > > > I've tried also making the first line > > UPDATE admin.subc, dblog1.eire but > > > > but still no luck, I've read through the Informix SQL > guide, but It doesn't > > give me an clues?? > > > > UPDATE admin.subc > > SET admin.subc.deal_key = "NVD" > > WHERE dblog1.eire.chas = admin.subc.CHAS_KEY; > > > > Any help greatly appreciated! > > > > Best Regards to all, > > Kirk > > -- > Stefan Weideneder > > Phone: +49 89/3565478-2 --------------- > --- Fax: +49 89/3565478-3 ------------- > ------ mailto:/stefan@weideneder.de --- > -------- http://www.weideneder.de ----- >
Hi Guys, Many thanks for your help with this one, I ran this query: UPDATE admin.subc SET admin.subc.deal_key = "NVD" WHERE admin.subc.chas_key IN ( SELECT chas FROM dblog1.eire ) and achieved my desired result! FYI, what I was trying to do was update a field in the main subc table when it matched a field in the eire table. Thanks again. //Kirk "Parker, Jack" <JParker@engage.com> wrote in message news:96hiku$4j4$1@news.xmission.com... > > > The EXISTS causes a hash join, the in causes a 'nested loop' type behaviour. > Use what is optimal for you. > > cheers > j. > > > -----Original Message----- > > From: Stefan Weideneder [mailto:stefan@weideneder.de] > > Sent: Thursday, February 15, 2001 3:19 PM > > To: informix-list@iiug.org > > Subject: Re: Update Statement > > > > > > Hi, > > > > how about this ? > > > > update admin.subc set admin.subc.deal_key = "NVD" > > where exists ( select 1 from dblog1.eire where > > dblog1.eire.chas = admin.subc.chas_key ); > > > > The statement might be faster if you would > > use the select-statement in an ordinary > > sub-query: > > > > where admin.subc.chas_key in ( select dblog1.eire.chas > > from dblog1.eire ); > > > > But I'm afraid that the select would return too much > > rows. > > > > Regards > > > > Stefan > > > > Kirk Davis wrote: > > > > > > Hi All, > > > > > > Hope someone out there can help me with this one, it's > > driving me mad! > > > > > > Does anyone have any idea, why the statement below would > > not work, I just > > > get the error " 522: Table (dblog1.eire) not selected in query." > > > > > > I've tried also making the first line > > > UPDATE admin.subc, dblog1.eire but > > > > > > but still no luck, I've read through the Informix SQL > > guide, but It doesn't > > > give me an clues?? > > > > > > UPDATE admin.subc > > > SET admin.subc.deal_key = "NVD" > > > WHERE dblog1.eire.chas = admin.subc.CHAS_KEY; > > > > > > Any help greatly appreciated! > > > > > > Best Regards to all, > > > Kirk > > > > -- > > Stefan Weideneder > > > > Phone: +49 89/3565478-2 --------------- > > --- Fax: +49 89/3565478-3 ------------- > > ------ mailto:/stefan@weideneder.de --- > > -------- http://www.weideneder.de ----- > >