howto query DELETE

[Christian Parpart]

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.

pgp2l9v1LOnci.pgp
Description: PGP signature