I'm no expert at all with interbase but... Could you create a view of the data you wish to change and run your SQL against that ? Don't know if IB supports this.
Rob Martin Software Engineer phone 03 377 0495 fax 03 377 0496 web www.chreos.com ----- Original Message ----- From: "Paul Mckenzie" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Wednesday, August 06, 2003 2:21 PM Subject: Re: [DUG]: joining in update query > Create, Execute, and drop a Temporary stored Proc to do the job - not > pritty, but faster! > > Regards > Paul McKenzie > Analyst Programmer > SMSS Ltd. > > ----- Original Message ----- > From: "Warren Slater (ASL)" <[EMAIL PROTECTED]> > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> > Sent: Wednesday, August 06, 2003 1:25 PM > Subject: RE: [DUG]: joining in update query > > > > Hi > > I don't think that she did > > It is perfectly legal (and usefull) in MS SQL to have a combined update > and > > join query all rolled into one. Unfortunately Interbase as far as I can > see > > (I spent quite a while looking) doesn't appear to allow this and so you > have > > to write the query as you have done with the join as a subquery. This is a > > real pain if you want to copy more than one field over as you have to do a > > separate subquery for each field to update. Also if there is no record > > returned in the subquery it will put a null into the field. (really > exciting > > when you were only expecting it to update the 5 records where matches > > exist!). To only update the records where matches do exist you need to add > a > > where exists to the update query. Finally the interbase engine seems to do > a > > lousy job of optimising the query (I think it separately executes each > > subquery) and updates on large datasets can be verrrrry slow. > > If anyone has any neat tricks to get around this then I would be very > > interested. > > Thanks > > Warren > > > > ie > > > > MS SQL > > > > update a > > set a.field1 = b.field1, a.field2=b.field2 > > from a,b > > where a.key=b.key > > > > INTERBASE > > > > update a > > set a.field1 = (select b.field1 from b where b.key = a.key), > > b.field1 = (select b.field2 from b where b.key = a.key) > > where exists (select b.field1 from b where b.key = a.key) > > > > > > -----Original Message----- > > From: James Low [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, 6 August 2003 12:54 p.m. > > To: Multiple recipients of list delphi > > Subject: RE: [DUG]: joining in update query > > > > > > Think you may have missed the select operator and some brackets ?? > > > > > > update TABLE1 > > set > > 1.updatefield = (Select 3.updatefield > > FROM TABLE1 1, TABLE2 2, TABLE3 3 > > Where 1.idfield = 2.idfield > > and 2.anotheridfield = 3.anotheridfield) > > > > James Low > > > > > > > > -----Original Message----- > > From: Tracey Maule [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, 6 August 2003 11:56 AM > > To: Multiple recipients of list delphi > > Subject: [DUG]: joining in update query > > > > Hi guys > > > > how do i do this in interbase?? > > > > update TABLE1 > > set > > 1.updatefield = (Select 3.updatefield > > FROM TABLE1 1, TABLE2 2, TABLE3 3 > > Where 1.idfield = 2.idfield > > and 2.anotheridfield = 3.anotheridfield) > > > > > > Tracey > > Software Developer / Web Master > > Logis > > [EMAIL PROTECTED] > > (025) 213-1065 > > > > > > > > This e-mail message has been swept for content and viruses. No viruses > were > > detected. Contact the Helpdesk on extension 9CIS (9247) for assistance, if > > required. > > > > > > > > > > > > Email disclaimer: This email and any attachments are confidential. If you > > are not the intended recipient, do not copy, disclose or use the contents > in > > any way. If you receive this message in error, please let us know by > return > > email and then destroy the message. Environment Bay of Plenty is not > > responsible for any changes made to this message and/or any attachments > > after sending. > > > > This e-mail has been checked for viruses and no viruses were detected. > > > > > ############################################################################ > ######### > > This e-mail message has been scanned for Viruses and Content and cleared > > by MailMarshal > > For more information please visit www.marshalsoftware.com > > > ############################################################################ > ######### > > -------------------------------------------------------------------------- > - > > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] > > Website: http://www.delphi.org.nz > > To UnSub, send email to: [EMAIL PROTECTED] > > with body of "unsubscribe delphi" > > Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/ > > > -------------------------------------------------------------------------- - > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] > Website: http://www.delphi.org.nz > To UnSub, send email to: [EMAIL PROTECTED] > with body of "unsubscribe delphi" > Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/ > > --------------------------------------------------------------------------- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi" Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
