Oninit cdi / Archive
Talk to an expert →

Need help on the correct SQL syntax

Posted in 2003

Topics: SQL Development & Query Writing

miyaki — — source: IIUG Forums & Mailing Lists

Hi all, What is the valid delete command in Informix that can referring to 2 tables? E.g. delete from table1 where table1.field1 = table2.field2 Similiar to the SQL statement as below used in Microsoft SQL server. DELETE FROM [Order Details] FROM [Order Details] AS OD JOIN Orders AS O ON OD.orderid = O.orderid WHERE customerid = 'VINET' Thanks & regards, Miyaki __________________________________ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/

↪ replying to miyaki
Clifton M. Bean — — source: IIUG Forums & Mailing Lists

I am thinking the following is what you want: delete from table1 where table1.field1 = (select table2.field2 from table2) HTHs. Clifton miyaki <lcib@yahoo.com> wrote: Hi all, What is the valid delete command in Informix that can referring to 2 tables? E.g. delete from table1 where table1.field1 = table2.field2 Similiar to the SQL statement as below used in Microsoft SQL server. DELETE FROM [Order Details] FROM [Order Details] AS OD JOIN Orders AS O ON OD.orderid = O.orderid WHERE customerid = 'VINET' Thanks & regards, Miyaki __________________________________ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/

↪ replying to miyaki
Danny Wright — — source: IIUG Forums & Mailing Lists

Try something like this: DELETE FROM table1 WHERE field1 IN (SELECT field2 FROM table2) or DELETE FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.field2 = table1.field1) ----- Original Message ----- From: "miyaki " <lcib@yahoo.com> To: <ids@iiug.org> Sent: Wednesday, December 17, 2003 1:22 AM Subject: Need help on the correct SQL syntax [2364] > Hi all, > > What is the valid delete command in Informix that can > referring to 2 tables? > > E.g. delete from table1 where table1.field1 = > table2.field2 > > Similiar to the SQL statement as below used in > Microsoft SQL server. > > DELETE FROM [Order Details] > FROM [Order Details] AS OD JOIN Orders AS O > ON OD.orderid = O.orderid WHERE customerid = 'VINET' > > > Thanks & regards, > Miyaki > > __________________________________ > Do you Yahoo!? > New Yahoo! Photos - easier uploading and sharing. > http://photos.yahoo.com/ >

↪ replying to miyaki
Mike Dunham-Wilkie — — source: IIUG Forums & Mailing Lists

At 12:22 AM 12/17/03, miyaki wrote: >Hi all, > >What is the valid delete command in Informix that can >referring to 2 tables? > >E.g. delete from table1 where table1.field1 = >table2.field2 Try: delete from table1 where field1 in (select field2 from table2); >Similiar to the SQL statement as below used in >Microsoft SQL server. > >DELETE FROM [Order Details] >FROM [Order Details] AS OD JOIN Orders AS O > ON OD.orderid = O.orderid WHERE customerid = 'VINET' > > >Thanks & regards, >Miyaki > >__________________________________ >Do you Yahoo!? >New Yahoo! Photos - easier uploading and sharing. >http://photos.yahoo.com/ ---------------------------------------- Michael Dunham-Wilkie, M.Sc., M.P.A. Senior Database Analyst Barrodale Computing Services Ltd. Tel: (250) 472-4372 Fax: (250) 472-4373 Web: http://www.barrodale.com Email: mike@barrodale.com ---------------------------------------- Mailing Address: P.O. Box 3075 STN CSC Victoria BC Canada V8W 3W2 Shipping Address: Hut R, McKenzie Avenue University of Victoria Victoria BC Canada V8W 3W2 ----------------------------------------

↪ replying to miyaki
Laila Larsen — — source: IIUG Forums & Mailing Lists

Hi Miyaki I can't see you have got an answer. The correct syntax is (in version IDS 9.3 I do think it works fine in other versions) delete from table1 where table1.field1 (e.g. key value) in (or not in) (select table2.field2 (e.g. key value) from table2) The following link, shows Informix Online Documentation: http://www-306.ibm.com/software/data/informix/pubs/library/ids_93.html Kind regards Laila Holt Larsen mailto:lhl@djoef.dk The Association of Danish Lawyers and Economists IT-department +45 33959944 -----Oprindelig meddelelse----- Fra: miyaki [mailto:lcib@yahoo.com] Sendt: 17. december 2003 09:23 Til: ids@iiug.org Emne: Need help on the correct SQL syntax [2364] Hi all, What is the valid delete command in Informix that can referring to 2 tables? E.g. delete from table1 where table1.field1 = table2.field2 Similiar to the SQL statement as below used in Microsoft SQL server. DELETE FROM [Order Details] FROM [Order Details] AS OD JOIN Orders AS O ON OD.orderid = O.orderid WHERE customerid = 'VINET' Thanks & regards, Miyaki __________________________________ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/

Related threads