thanks warren unfortunately your help didnt help because i am stuck with needing two where clauses from 3 tables, and interbase will not allow joins in subqueries
looks like i will be changing 1540 records by hand ----- 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/
