howto query DELETE all records having invalid table refs
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
howto query DELETE all records having invalid table refs
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. pgpRXubTioFGf.pgp Description: PGP signature
Re: howto query DELETE all records having invalid table refs
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.
Re: howto query DELETE all records having invalid table refs
Hi, Try, mysql delete from GbEntries where sender not in (select * from Accounts); Thanks ViSolve DB Team. - Original Message - From: Christian Parpart [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 20, 2006 3:11 PM Subject: howto query DELETE all records having invalid table refs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
