ianh Posted February 4, 2009 Share Posted February 4, 2009 Hi, I have 2 tables.... 'TABLE A' contains around 1 million email records (with IDs) 'TABLE B' contains 4000 email records (without IDs) I want to find and delete from 'TABLE A' the 4000 records that are found in 'TABLE B' . I tried doing a join but the query seems to take forever and has even crashed the server Please can someone tell me the quickest and easiest way of doing this? ??? Your help and advice would be much appreciated! Thanks. Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/ Share on other sites More sharing options...
JonnoTheDev Posted February 4, 2009 Share Posted February 4, 2009 Place an index on A email field. Select rows from B. Loop through rows and delete from A where email address = B row email You are better running through the command line. Dont try in a web browser. Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/#findComment-754200 Share on other sites More sharing options...
ianh Posted February 4, 2009 Author Share Posted February 4, 2009 Place an index on A email field. Select rows from B. Loop through rows and delete from A where email address = B row email You are better running through the command line. Dont try in a web browser. Thanks Neil. Any chance of the query code? Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/#findComment-754231 Share on other sites More sharing options...
nadeemshafi9 Posted February 4, 2009 Share Posted February 4, 2009 DELETE FROM table_a WHERE table_a.email = (SELECT table_b.email FROM table_b); mysql 4.2 > The inner most query is performed first so it will look at the first record in table B and find the corisponding record in table a then delete it and so on. Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/#findComment-754248 Share on other sites More sharing options...
daryy Posted February 4, 2009 Share Posted February 4, 2009 Good idea! What's going on Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/#findComment-754269 Share on other sites More sharing options...
nadeemshafi9 Posted February 4, 2009 Share Posted February 4, 2009 Good idea! What's going on i dont get it ? lol Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/#findComment-754282 Share on other sites More sharing options...
Maq Posted February 4, 2009 Share Posted February 4, 2009 Good idea! What's going on i dont get it ? lol You're not the only one... @OP Can I ask why you are storing emails in a separate table? There should be an easier way that's also more efficient. Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/#findComment-754289 Share on other sites More sharing options...
nadeemshafi9 Posted February 4, 2009 Share Posted February 4, 2009 Good idea! What's going on i dont get it ? lol You're not the only one... @OP Can I ask why you are storing emails in a separate table? There should be an easier way that's also more efficient. ahhh efficiency something im learning about in the world of SQL, only done 4 units on SQL thats spread over 2 years 1 hour a week, i got 31 out of 31 but i cheated on the last one which was a massive query i emailed myself the answer ahahahahahaha and then looked at it on the pc i was doing my test on bwhgahahahaha. i dint do 3 years of hard work to get 30 out of 31 Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/#findComment-754290 Share on other sites More sharing options...
aschk Posted February 5, 2009 Share Posted February 5, 2009 DELETE FROM table_a WHERE table_a.email = (SELECT table_b.email FROM table_b); mysql 4.2 > Unfortunately the quoted above will not work, replace the "=" for "IN" and it'll work, however it would be advisable to place an index on the email columns to make this query more efficient. Link to comment https://forums.phpfreaks.com/topic/143746-find-and-delete-4000-email-records-without-ids/#findComment-755351 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.