Deleting records from a parent table

How would I delete records from a parent table if it had no children? I know
this seems backwards but that is what I need to do.
Like....
delete from tblParentRecords if count of tblChildRecords = 0 or something
like this.
ThanksHi Craig,
Is it possible for you to provide DDL/sample record for your parent and
child tables?
Here is a small smaple for your reference:
create table p1
(
cp1 int primary key
)
GO
INSERT INTO p1 VALUES (1)
INSERT INTO p1 VALUES (2)
INSERT INTO p1 VALUES (3)
INSERT INTO p1 VALUES (4)
GO
create table p2
(
cc1 int foreign key references p1(cp1)
)
INSERT INTO p2 values (2)
INSERT INTO p2 values (3)
INSERT INTO p2 values (2)
select * from p1
where cp1 not in (select cc1 from p2)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Criag
Try the NOT exists
delete from tblparent P1 where NOT EXISTS(select * from tblChild C1 where
C1.Key = P1.Key)
kind regards
Greg O
"Craig" <NoSpam@.hotmail.com> wrote in message
news:OyKR0rJ7FHA.2816@.tk2msftngp13.phx.gbl...
> How would I delete records from a parent table if it had no children? I
> know this seems backwards but that is what I need to do.
> Like....
> delete from tblParentRecords if count of tblChildRecords = 0 or something
> like this.
>
> Thanks
>
>|||I have many fields in the relationship.
The SQL below gets me the records I want to delete but I don't know how to
convert this to a delete statement.
tblBoilersTestDataBoilerWaterAveragesPer
Day is the parent table
tblBoilersTestDataBoilerWater is the child table
tblBoilersTestDataBoilerWater.ReadingDate comes up NULL because there are no
child records
SELECT tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDate
FROM tblBoilersTestDataBoilerWaterAveragesPer
Day LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPer
Day.LocationID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.SystemID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.BoilerNumber =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
How do I convert this to a delete statement or create a statement that
deletes parent record based on the number of child records = 0?
Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:6RCZpoM7FHA.3764@.TK2MSFTNGXA02.phx.gbl...
> Hi Craig,
> Is it possible for you to provide DDL/sample record for your parent and
> child tables?
> Here is a small smaple for your reference:
> create table p1
> (
> cp1 int primary key
> )
> GO
> INSERT INTO p1 VALUES (1)
> INSERT INTO p1 VALUES (2)
> INSERT INTO p1 VALUES (3)
> INSERT INTO p1 VALUES (4)
> GO
> create table p2
> (
> cc1 int foreign key references p1(cp1)
> )
> INSERT INTO p2 values (2)
> INSERT INTO p2 values (3)
> INSERT INTO p2 values (2)
> select * from p1
> where cp1 not in (select cc1 from p2)
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Craig,
I modified my sample for your reference
create table p1
(
cp1 int primary key,
cp2 int,
cp3 int
)
GO
INSERT INTO p1 VALUES (1,1,1)
INSERT INTO p1 VALUES (2,2,2)
INSERT INTO p1 VALUES (3,3,3)
INSERT INTO p1 VALUES (4,4,4)
GO
create table p2
(
cc1 int,
cc2 int,
cc3 int
)
INSERT INTO p2 values (2,2,2)
INSERT INTO p2 values (3,2,2)
INSERT INTO p2 values (4,4,4)
select * from p1, p2
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
select * from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and
p1.cp3 = p2.cc3
delete p1
from p1 left join p2 on p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 =
p2.cc3
where p1.cp1 = p2.cc1 and p1.cp2 = p2.cc2 and p1.cp3 = p2.cc3
--
You may perform the statement below, NOTE that please make it full backuped
and you have tested before
DELETE tblBoilersTestDataBoilerWaterAveragesPer
Day
FROM
tblBoilersTestDataBoilerWaterAveragesPer
Day LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPer
Day.LocationID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.SystemID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.BoilerNumber =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPer
Day.ReadingDateYear =
tblBoilersTestDataBoilerWater.ReadingDateYear
WHERE (tblBoilersTestDataBoilerWater.ReadingDate IS NULL)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.