MySQL - Delete Records If Exists In Another Table

Databases
1

Hi Guys,

I have 1 table called cleaned and another table called Failed. How do i go about deleting records from the table called cleaned if the same record exists on the table called Failed?

Both fields in the table it will be looking at is called Email.

Any help would be great.

Thanks!

2

I’ve used the following:

delete from CLEANED where Email in (select Email from FailedSMTP)

I have over 1.6 million records in the table called CLEANED and its taking a long time, is there a better way of me doing this to increase the speed?

Thanks

3

use a joined delete query

have a look at the example in the manual under DELETE syntax, and let me know if you have any question

4

This?


DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

If so that Query has puzzled me as i dont get it. :frowning:

5

Or would this be my answer?


delete CLEANED.*
from CLEANED
left join FailedSMTP
on Cleaned.Email = FailedSMTP.Email
where FailedSMTP.Email is not null
6

that’s close :slight_smile:

use an INNER JOIN and lose the WHERE clause

7

This?


delete CLEANED.*
from CLEANED
INNER JOIN FailedSMTP
on Cleaned.Email = FailedSMTP.Email
8

yeah, that looks fine, go ahead and take a backup first and then try it

9 
Id	User	Host	db	Command	Time	State	Info
182	root	localhost	loud_data	Query	25551	Sending data	delete CLEANED.* from CLEANED INNER JOIN FailedSMTP on CLEANED.Email = FailedSMTP.Email

Its taking ages how do i know if its actually doing it or if its frozen?

10

I’ve just restarted MySQL and canceled the Query as it was still running, i went into PHPMYADMIN and everything was the same, no data was deleted. Is there any other way?

Below is my dedicated server information, I’m only hosting 1 website on this server and cPanel/WHM.

Processor #1
Vendor
GenuineIntel
Name
Intel(R) Core™ i5 CPU 760 @ 2.80GHz
Speed
1197.000 MHz
Cache
8192 KB
Processor #2
Vendor
GenuineIntel
Name
Intel(R) Core™ i5 CPU 760 @ 2.80GHz
Speed
1197.000 MHz
Cache
8192 KB
Processor #3
Vendor
GenuineIntel
Name
Intel(R) Core™ i5 CPU 760 @ 2.80GHz
Speed
1197.000 MHz
Cache
8192 KB
Processor #4
Vendor
GenuineIntel
Name
Intel(R) Core™ i5 CPU 760 @ 2.80GHz
Speed
1197.000 MHz
Cache
8192 KB

Memory Information
Memory for crash kernel (0x0 to 0x0) notwithin permissible range
Memory: 4034840k/4980736k available (2592k kernel code, 149476k reserved, 1649k data, 224k init)