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

> 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 ############################################################################# # Lester Knutsen lester@access.digex.net # # Advanced DataTools Corporation Voice: 703-256-0267 # # Grant group privileges for Informix databases with DB Privileges # # Visit our Web page: http://www.access.digex.net/~lester # #############################################################################