Find and Delete 4000 email records (without IDs) ?

[ianh]

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.

[JonnoTheDev]

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.

[ianh]

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?

[nadeemshafi9]

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.

[daryy]

Good idea! What's going on

[nadeemshafi9]

Good idea! What's going on

 

i dont get it ? lol

[Maq]

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.

[nadeemshafi9]

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

[aschk]

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.