Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-17
5
Medium Priority
?
1,527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

721 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