The following statement should do the trick: delete from GBEntries t1 where (select count(*) from Accounts nt1 where nt1.ID=t1.Sender)=0
Performance of this query will be significantly improved if there is an index on Accounts.ID. HTH, Panos. On 12/20/06, Christian Parpart <[EMAIL PROTECTED]> wrote:
Hi all, there is a live before foreign keys, but now I'm using mysql 5.0 ;) how where I'm about to convert my db schema to make use of foreign keys (via innodb) I am enforced to delete all invalid entries before. however, I can do this using a huch select loop with a test within to delete all whose referencing record in a parent table doesn't exist anylonger. but I'd like to do this cleanup within a single DELETE query (to speedup the schema upgrade phase), but what kind of WHERE clause shall I use here? TABLE Accounts ( ID INTEGER NOT NULL auto_increment, -- ... ); TABLE GbEntries ( ID INTEGER NOT NULL auto_increment Sender INTEGER NOT NULL, -- references an account (the sender) -- ... FOREIGN KEY (Sender) REFERENCES Accounts(ID); ); I can add the foreign key to GbEntries only when all invalid records (pointing to not anylonger existing `Accounts` records) have been wiped out of GbEntries table. DELETE GbEntries Accounts WHERE Sender "NOT IN Accounts.ID"; but how's the proper SQL syntax for this? Thanks in advance, Christian Parpart.
-- Panos Tsapralis, Senior Software / Systems Engineer, +306948076902, Athens, GREECE.
