MySQL Delete with a LEFT JOIN

MySQL Delete with a LEFT JOIN2005-09-20

A database I was working on had some orphanned records that I was able to see using a LEFT JOIN.

SELECT EventID FROM EventLog e LEFT JOIN CustInfo c USING (CustID) WHERE c.CustID IS NULL;

Deleting those records is a little more complicated that changing the SELECT to DELETE. Specifically you have to tell MySQL what table you want to delete the records from (i.e. Eventlog.*) otherwise it gets confused about what table to delete the records from.

DELETE e.* FROM EventLog e LEFT JOIN CustInfo c USING (CustID) WHERE c.CustID IS NULL;

Information borrowed from ElectricToolbox.

Leave A Reply - 29 Replies

Replies

jamil shaikh 2006-02-02 01:59am - jamil_webmaster@... - Logged IP: 217.33.62.2

Thanks ever so much guys

the delete query works wonders and saved my job!!!

shp 2007-12-18 07:37am - No Email - Logged IP: 38.116.28.20

Thank you to you too!! I google this problem and It is really work with my job.

neworld 2008-01-29 09:37am - No Email - Logged IP: 78.60.204.106

Thanks a lot

Nick Evans 2008-03-17 10:24am - nick@... - Logged IP: 81.149.117.103

Earnie 2008-03-20 07:21am - No Email - Logged IP: 155.70.141.45

I had to use the form of but thanks for the information:

DELETE e.* FROM EventLog e LEFT JOIN CustInfo c USING (CustID) WHERE c.CustID IS NULL;

as if 2008-03-24 11:01am - No Email - Logged IP: 70.180.167.136

Yup use the short name for the table (e) instead of the full name (EventLog), and you did it. Thanks for the help!

Andrew4fr 2008-07-03 03:26pm - No Email - Logged IP: 93.81.9.152

Also works DELETE EventLog FROM EventLog e LEFT JOIN CustInfo c USING (CustID) WHERE c.CustID IS NULL;

Anand 2008-08-12 02:55am - No Email - Logged IP: 122.169.120.3

Thank You, Boss

Mathias 2008-10-23 01:52am - No Email - Logged IP: 213.23.209.194

Thank you SO (!) much for sharing this

thanks 2008-11-13 07:07pm - No Email - Logged IP: 218.5.2.219

does not work in wright way but thank you all the same !

ben 2009-01-23 05:35am - No Email - Logged IP: 69.27.56.125

Very helpful, thanks

kamm 2009-02-25 02:27am - No Email - Logged IP: 195.238.48.13

Thanks - useful info and worked for me...

kixs 2009-04-25 02:47pm - No Email - Logged IP: 80.137.3.124

Worked for me also.. Thanks :P

Jabop 2009-05-29 02:08pm - No Email - Logged IP: 69.17.50.227

worked for me also, but the table you're deleting from must be referenced by its alias and not the full table name, otherwise you'll this error:

ERROR 1109 (42S02): Unknown table 'TABLE_NAME' in MULTI DELETE

Beauford 2010-04-23 09:47am - No Email - Logged IP: 99.249.60.143

Doesn't work.

Error Code : 1054 Unknown column 'userid' in 'from clause'

For me userid is CustID

Beauford 2010-10-15 11:31am - No Email - Logged IP: 99.249.60.143

Still Nothing, and I have tried numerous code based on what has been said here, so I must assume this can't be done in the version of MySQL I am using, which is 5.x. Anyone????

Here are a couple I tried:

DELETE v. FROM poll_votes v LEFT JOIN polls p USING (poll_id) WHERE v.poll_id = p.id; DELETE v. FROM poll_votes v LEFT JOIN polls p USING (id) WHERE v.poll_id = p.id; DELETE poll_votes. FROM poll_votes v LEFT JOIN polls p USING (poll_id) WHERE v.poll_id = p.id; DELETE poll_votes FROM poll_votes v LEFT JOIN polls p USING (poll_id) WHERE v.poll_id = p.id; DELETE FROM poll_votes v LEFT JOIN polls p USING (poll_id) WHERE v.poll_id = p.id;

Anyone help out with the right syntax as I have no idea!!

Thanks

Egy 2010-10-25 06:37am - No Email - Logged IP: 41.237.125.84

DELETE tag.* FROM tag LEFT JOIN photo ON tag.photo = photo.photo_id WHERE photo_id IS NULL;

Works on MySQL 5.0.51a

mr.magicsock 2011-07-04 01:50pm - No Email - Logged IP: 82.8.238.126

you saved my life and my job, and my wife and kids too YOU'RE AWESOME.

Nate Andersen 2011-07-09 10:24am - No Email - Logged IP: 208.120.240.148

Thanks for this tip!

Andreka 2011-12-24 09:01pm - No Email - Logged IP: 114.79.52.198

makasih mas.. matur nuwun

oranzo 2012-01-18 03:50pm - No Email - Logged IP: 193.77.222.182

Thanks :)

Aaron R 2012-02-02 01:24pm - No Email - Logged IP: 199.58.143.70

Big help, thanks for sharing!=)

Don Clemento 2012-02-13 06:27am - No Email - Logged IP: 212.47.176.130

It helped me a lot! Thank you guys!

Saurabh 2012-06-10 09:31pm - No Email - Logged IP: 180.211.108.58

Thank you so much. It helps me a lot.

apoca 2012-12-18 06:46pm - No Email - Logged IP: 31.179.100.249

Thxs, good to know this, days before the end of the world, I'm currently learning as much as possible to die as a smart man!

ben 2013-01-02 11:39am - No Email - Logged IP: 207.245.202.198

Thanks

kalai 2013-02-01 01:40am - No Email - Logged IP: 61.16.174.210

good solution with explanation.thanks a lot

steve 2013-03-05 06:05am - No Email - Logged IP: 90.15.231.133

Thanks, works great!!

eLo 2017-09-27 07:32am - No Email - Logged IP: 185.197.57.17

As Earnie I had to add e. instead of EventLog. and worked great, thanks for the ease!

All content licensed under the Creative Commons License