delete parent records where child fields are blank

[efandango]

I have a Parent table and child table.

I want to delete any Parent record that has an empty sub child record. Not
having a sub is determined by the sub childs fields being null. The empty
fields in the sub child table are:

Run_Direction
Run_waypoint
Postcode


These are my two tables:

Parent table: tbl_Getrounds

Field:
GetRound_ID (Autonumber)
GetRoundPoint (text)


Child Table: tbl_Getround_Detail

Fields:
GetRound_Detail_ID (Autonumber)
GetRound_ID (linked to parent PK)
Run_Direction
Run_waypoint
Postcode


This is my SQL:

SELECT tbl_Getrounds.GetRound_ID, tbl_Getrounds.GetRoundPoint,
tbl_Getround_Detail.Run_Direction, tbl_Getround_Detail.Run_waypoint,
tbl_Getround_Detail.Postcode
FROM tbl_Getrounds LEFT JOIN tbl_Getround_Detail ON
tbl_Getrounds.GetRound_ID = tbl_Getround_Detail.GetRound_ID
WHERE (((tbl_Getround_Detail.Run_Direction) Is Null) AND
((tbl_Getround_Detail.Run_waypoint) Is Null) AND
((tbl_Getround_Detail.Postcode) Is Null))
ORDER BY tbl_Getrounds.GetRoundPoint;

 

[John W. Vinson]

I have a Parent table and child table.

I want to delete any Parent record that has an empty sub child record. Not
having a sub is determined by the sub childs fields being null. The empty
fields in the sub child table are:

Run_Direction
Run_waypoint
Postcode


These are my two tables:

Parent table: tbl_Getrounds

Field:
GetRound_ID (Autonumber)
GetRoundPoint (text)


Child Table: tbl_Getround_Detail

Fields:
GetRound_Detail_ID (Autonumber)
GetRound_ID (linked to parent PK)
Run_Direction
Run_waypoint
Postcode


This is my SQL:

SELECT tbl_Getrounds.GetRound_ID, tbl_Getrounds.GetRoundPoint,
tbl_Getround_Detail.Run_Direction, tbl_Getround_Detail.Run_waypoint,
tbl_Getround_Detail.Postcode
FROM tbl_Getrounds LEFT JOIN tbl_Getround_Detail ON
tbl_Getrounds.GetRound_ID = tbl_Getround_Detail.GetRound_ID
WHERE (((tbl_Getround_Detail.Run_Direction) Is Null) AND
((tbl_Getround_Detail.Run_waypoint) Is Null) AND
((tbl_Getround_Detail.Postcode) Is Null))
ORDER BY tbl_Getrounds.GetRoundPoint;

And what's happening?

You don't want a LEFT JOIN unless you want to delete parent records for which
no child record exists at all: is that the case?

And if you have referential integrity enforced, you will not be able to delete
parent records which would leave "orphan" child records. Would it make sense
to first delete those tbl_Getround_Detail records with NULL values in the
fields, and then delete those parent records with no matching child records?

 

[John Spencer]

First you would need to delete the child table records

DELETE
FROM tbl_GetRound_Detail
WHERE Run_Direction is Null
AND RuN_Waypoint is Null
AND PostCode is Null

THEN you would delete any records in tbl_GetRounds that does not have a
child record.

Delete
FROM tbl_GetRounds
WHERE GetRound_ID in
(SELECT temp.GetRound_ID
FROM tbl_GetRounds as Temp LEFT JOIN tbl_Get_Round_Detail as GRD
ON Temp.GetRound_ID =GRD.GetRound_ID
WHERE GRD.GetRound_ID is Null)

This would preclude you deleting a record from tbl_GetRounds that has
any valid records still attached.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================