Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-17
5
Medium Priority
?
1,536 Views
Last Modified: 2008-10-22
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));
0
Comment
Question by:mindwarpltd
  • 2
  • 2
5 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22742435
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
 

Author Comment

by:mindwarpltd
ID: 22742480
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
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22742531
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
 

Accepted Solution

by:
mindwarpltd earned 0 total points
ID: 22742579
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
 
LVL 6

Expert Comment

by:carlsiy
ID: 22742878
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question