Re: delete from table where (select from other_table with composite key)

Lester Knutsen (lester@access.digex.net) wrote: : > Jochen Kornitzky asked: : > I've got a table and a tmp_table with the same column definitions. The index : > has a unique key *composited* of field1 and field2 and indexes on every field. : > I want to delete every row from table whose key (composed from : > field1 and field2) is contained in tmp_table. : > : < Stuff deleted to save space > : Jochen, : If you are NOT using table fragmentation in 7.1 and can lock the table : rowid's will work and be very quick. Here's how I would approch it : using rowid's: : lock table; { this makes sure the rowids don't change while you : are processing the data } : select rowid row_no {need an alias for rowid }, : field1, field2, ... : from table where ...... : into temp temp_table; : delete from table where rowid in ( select row_no from temp_table ); : unlock table; { or commit work } : Hope this helps : Regards - Lester Another solution is: DELETE FROM table1 WHERE 1 IN ( SELECT 1 FROM table2 WHERE table2.column1 = table1.column1 AND table2.column2 = table1.column2 ); ___ ___ Principal Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111