delete records

I am having trouble trying to figure out how to delete some records. I have
the following query:
select * from p join ppt on p.PatNo=ppt.PatNo
where p.DayTime>=ppt.Discharge
this returns 1,000 records. These are the records I want to delete from p.
I created this delete query:
delete from p
where exists (select * from p join ppt on p.PatNo=ppt.PatNo
where p.DayTime>=ppt.Discharge)
This query deletes all 16,000 records from p. Logically, what am I missing?
Thanks
DionTry this
DELETE p
FROM pjoin ppt on p.PatNo=ppt.PatNo where p.DayTime>=ppt.Discharge
"Dion" wrote:

> I am having trouble trying to figure out how to delete some records. I ha
ve
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from p
.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I missin
g?
> Thanks
> Dion
>
>|||Try this:
DELETE P
FROM P JOIN PPT
ON P.PATNO =PPT.PATNO
WHERE P.DAYTIME>=PPT.DISCHARGE
I usually wrap admin statements like this in a BEGIN TRAN then query the
table to see if the correct results are obtained if so COMMIT TRAN if not
ROLLBACK TRAN.
HTH
Jerry
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:DB2A229F-F7E6-4107-A646-F4C915DDE1B8@.microsoft.com...
>I am having trouble trying to figure out how to delete some records. I
>have
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from
> p.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I
> missing?
> Thanks
> Dion
>
>|||On Fri, 30 Sep 2005 14:45:02 -0700, Dion wrote:
>I am having trouble trying to figure out how to delete some records. I hav
e
>the following query:
>select * from p join ppt on p.PatNo=ppt.PatNo
>where p.DayTime>=ppt.Discharge
>this returns 1,000 records. These are the records I want to delete from p.
>I created this delete query:
>delete from p
>where exists (select * from p join ppt on p.PatNo=ppt.PatNo
>where p.DayTime>=ppt.Discharge)
>This query deletes all 16,000 records from p. Logically, what am I missing?[/color
]
Hi Dion,
You didn't correlate the subquery to the main query. The subquery runs
by itself, so it will return the same result (1,000 rows) for each row
in the outer query - and that means that the EXISTS is true for each row
in p.
DELETE FROM p
WHERE EXISTS (SELECT *
FROM ppt
WHERE p.PetNo = ppt.PatNo
AND p.DayTime >= ppt.Discharge)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try:
DELETE
FROM P
WHERE P.<PK column name> IN (SELECT P.<PK COlum name>
FROM P
INNER
JOIN PPT
ON
P.PatNo = ppt.PatNo
WHERE P.DayTime >=
PPT.DIscharge )
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:DB2A229F-F7E6-4107-A646-F4C915DDE1B8@.microsoft.com...
> I am having trouble trying to figure out how to delete some records. I
have
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from
p.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I
missing?
> Thanks
> Dion
>
>