delete records

30 Sep 2005 9:45 PM

Dion

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

30 Sep 2005 4:26 PM

David Frommer

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 )
Show quote

"Dion" <D***@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
>
>
>

30 Sep 2005 10:05 PM

Steve

Try this

DELETE p
FROM pjoin ppt on p.PatNo=ppt.PatNo where p.DayTime>=ppt.Discharge

Show quote

"Dion" wrote:

> 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
>
>
>

30 Sep 2005 10:09 PM

Jerry Spivey

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
Show quote

"Dion" <D***@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
>
>
>

30 Sep 2005 10:45 PM

Hugo Kornelis

On Fri, 30 Sep 2005 14:45:02 -0700, Dion wrote:

Show quote

>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?

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)