Solved

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

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

626 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