Solved

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

Posted on 2008-10-17
5
1,515 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql date time 14 52
clicking to find my folders on wamp 8 47
Output in PHP throwing alignment of data off issue 12 76
Get data from two MySQL tables 6 66
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 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