Jump to content

Find and Delete 4000 email records (without IDs) ?


Recommended Posts

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  :o

 

Please can someone tell me the quickest and easiest way of doing this?  ???

 

Your help and advice would be much appreciated!

 

Thanks.

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?

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.

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

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.