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.
Thanks
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 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.
tblBoilersTestDataBoilerWaterAveragesPerDay is the parent table
tblBoilersTestDataBoilerWater is the child table
tblBoilersTestDataBoilerWater.ReadingDate comes up NULL because there are no
child records
SELECT tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDate
FROM tblBoilersTestDataBoilerWaterAveragesPerDay LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPerDay.Locati onID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.System ID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Boiler Number =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateYear =
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 tblBoilersTestDataBoilerWaterAveragesPerDay
FROM
tblBoilersTestDataBoilerWaterAveragesPerDay LEFT OUTER JOIN
tblBoilersTestDataBoilerWater ON
tblBoilersTestDataBoilerWaterAveragesPerDay.Locati onID =
tblBoilersTestDataBoilerWater.LocationID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.System ID =
tblBoilersTestDataBoilerWater.SystemID AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Boiler Number =
tblBoilersTestDataBoilerWater.BoilerNumber AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateMonth =
tblBoilersTestDataBoilerWater.ReadingDateMonth AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateDay =
tblBoilersTestDataBoilerWater.ReadingDateDay AND
tblBoilersTestDataBoilerWaterAveragesPerDay.Readin gDateYear =
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.
sql