How to delete orphan records via foreign key not matched in another table

I've been cleaning up my main table and have deleted lots of records.

I've now realised that I have orphan records in another table.

This is my select query which identifies the orphaned records..

SELECT Authors.AuthorID, Authors.FirstName
FROM Authors LEFT JOIN Pads ON Authors.AuthorID = Pads.AuthorID
WHERE (((Pads.AuthorID) Is Null));

I thought this would work...

Delete FROM Authors LEFT JOIN Pads ON Authors.AuthorID = Pads.AuthorID

But I get this error.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN Pads ON Authors.AuthorID = Pads.AuthorID
WHERE (((Pads.AuthorID) Is N' at line 1

Can you provide me with a delete query which will do this or a solution to solve the problem.
WHERE (((Pads.AuthorID) Is Null));
mindwarpltdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
I believe the real reason is that you want to isolate the delete to just the one table (Authors). So what you will want to do is use a subquery in the WHERE clause similar to the following.

FIRST, Are you trying to delete from the authors table or from the PADS table?

DELETE FROM AUTHORS
WHERE Authors.AuthorID = (SELECT Pads.AuthorID From PADS Where PADS.AuthorID Is Null)
...which could actually become
DELETE FROM AUTHORS WHERE Authors.AuthorID Is Null

if you want to delete from PADS
DELETE FROM PADS Where PADS.AuthorID Is Null

hope that gets you going.

Dan


0
mindwarpltdAuthor Commented:
I want to delete from the authors table.

I converted your first delete query into a select query, for safety.

SELECT *
FROM AUTHORS
WHERE Authors.AuthorID = (
SELECT Pads.AuthorID
FROM PADS
WHERE PADS.AuthorID IS NULL )
LIMIT 0 , 30

And it returned no records.

DELETE FROM AUTHORS WHERE Authors.AuthorID Is Null

This won't work as it has to relate to the pads table.
0
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
ok, try this last one

SELECT *
FROM AUTHORS
WHERE Authors.AuthorID = (
SELECT Authors.AuthorID
FROM Authors LEFT JOIN Pads ON Authors.AuthorID = Pads.AuthorID
WHERE (((Pads.AuthorID) Is Null))
)
0
mindwarpltdAuthor Commented:
I can do a select query, its the delete query I have problems with.

I figured out a solution.

Update Authors LEFT JOIN Pads ON Authors.AuthorID = Pads.AuthorID
Set Maint = 'D'
WHERE (((Pads.AuthorID) Is Null));

DELETE FROM `Authors` WHERE Maint = 'D';
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carlsiyCommented:
why not do this?

delete from
authors
where authors.authorID
in
(SELECT Authors.AuthorID
FROM Authors LEFT JOIN Pads ON Authors.AuthorID = Pads.AuthorID
WHERE (((Pads.AuthorID) Is Null)))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.