Deleting Parents with no Children 2

[swaybright]

Hi All,

I'm trying to set up a delete query that deletes all the parent records that have no child records. But I'm getting an error that says "could not delete from specified tables"

Generically, my sql is:

DELETE parenttable.*,childtable.foriegnkey
FROM parenttable
LEFT JOIN Childtable ON parenttable.primarykey=childtable.foriegnkey
WHERE (((childtable.foriegnkey) Is Null));

This is obviously wrong, but I don't know how to correct it. The corresponding select query works just fine.
Does anyone have any ideas?

TIA
Shane

 

[AmritPaulSingh]

The syntax for a delete sql statement is

Delete from table_name where delete_crtieria

If there are no child records for the record youi are trying to delete, you do not need to enter the child table in the sql. It will work fine just like that.

Let me know if it doesn't work.

Amrit

 

[swaybright]

Thanks for the reply.

The problem is I have to delete ONLY parent records without child records. If I don't tell it to find the records without children, it will delete all parent records--right?

Shane

 

[Golom]

Try

DELETE *
FROM parenttable
WHERE parenttable.primarykey IN 
      (Select parenttable.primarykey 
       FROM parenttable LEFT JOIN Childtable ON
            parenttable.primarykey=childtable.foriegnkey
            WHERE childtable.foriegnkey Is Null;

 

[swaybright]

Thanks Golom!!

That worked perfectly.
Here's a star!

Shane