Re: SQL advice please

Steve Dont know if this will work, but you could try... DELETE FROM table_a WHERE (a, b) IN ((SELECT a, b from table_b)) Of course, you would want to enclose the statement in a BEGIN WORK...ROLLBACK WORK during testing, so you dont delete data that you wanted not to delete, and only if it works then replace the ROLLBACK WORK with COMMIT WORK (or even remove the BEGIN WORK...ROLLBACK WORK pair). HTH Sujit Steve Barnes <sgbarnes99@hotmail.com> on 02/29/2000 05:24:39 AM To: informix-list@iiug.org cc: Subject: SQL advice please Morning all and Obnoxio, I am trying to delete some rows from a table where the same columns exist in another table, but there are two columns to check. I want to delete all rows in table_a where col_a and col_b are in table_b. The only way I have so far is as follows DELETE FROM table_a WHERE trim(col_a)||trim(col_b) IN (SELECT trim(col_a)||trim(col_b) from table_b) Does anyone know of a better way? Thanks Steve ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

Sujit.Pal@bankofamerica.com wrote: > > Steve > > Dont know if this will work, but you could try... > > DELETE FROM table_a WHERE (a, b) IN > ((SELECT a, b from table_b))[SNIP] That will not work. The sub-query that is the object of an IN clause can only return ONE column or calculated value. You either have to use an EXISTS clause or have the sub-select return ROWID from a join: DELETE FROM table_a WHERE rowid in ( SELECT a.rowid FROM table_a a, table_b b WHERE a.a = b.a and a.b = b.b); However, because of the large number of locks that may be needed to complete this delete one may want to use an application and break the query up into smaller pieces as my dbdelete utility does. Dbdelete is part of the package utils2_ak in the IIUG Software Repository. -- Art S. Kagel & Family kagel@erols.com