mindwarpltd
asked on
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));
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));
ASKER
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.
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.
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))
)
SELECT *
FROM AUTHORS
WHERE Authors.AuthorID = (
SELECT Authors.AuthorID
FROM Authors LEFT JOIN Pads ON Authors.AuthorID = Pads.AuthorID
WHERE (((Pads.AuthorID) Is Null))
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)))
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)))
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