DELETE QUERY

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

guaro555 - 28 Aug 2006 18:56 GMT

Ok gurus

I need your help, I am new to sql and access database, so easy on me, here
is my need:

I have a table named table1234, within this table I have a field named
status and a field named Doc# number, the status value are only three
possibility (PT,PY,CH) there can be the same document number for all three
or It could be just for two of them or one at the matter.

Status doc
PT 1
PY 1
CH 1
PT 2
PY 2
CH 3

I would like to run a delete query to delete only doc numbers ones, as is the
only number on the examle that has the three status, I dont one to keep one I
would like to delete all records that are associated with doc number 1, so
the table should look like this

Status doc
PT 2
PY 2
CH 3

Thanks a bunch gurus

Reply to this Message

geebee - 28 Aug 2006 19:22 GMT

hi,

First, create a backup of your table just in case someone asks you what
happened or wants to see the old data (happens all the time).

here is the query:
DELETE tablename.*
FROM tablename
WHERE (((tablename.doc)='1'));

> Ok gurus
>
[quoted text clipped - 25 lines]
>
> Thanks a bunch gurus

Reply to this Message

guaro555 - 28 Aug 2006 19:47 GMT

thanks Geebee for your promt reply, but I have a question

at my example I know the doc number but when you have 20000 records this is
not very efficient, Am I missing something here, sorry new to this whole sql
deal, I would like to chnage one for something more global

Thanks a bunch

>hi,
>
[quoted text clipped - 11 lines]
>>
>> Thanks a bunch gurus

Reply to this Message

John Spencer - 28 Aug 2006 19:41 GMT

Do you need to delete if a doc has 3 status records or must they be three
different status records?

DELETE DISTINCTROW
FROM [Table1234] as T
WHERE T.Doc in
(SELECT Temp.Doc
FROM [Table1234] as Temp
GROUP BY Doc
HAVING Count(Doc) >=3)

If you need to have each one of 3 different status, then the problem is a
bit more difficult. If the only status codes are PT, PY, and CH then you
don't need the where clause in the embedded subquery - WHERE Status in
("PT","PY","CH")

DELETE DISTINCTROW
FROM [Table1234] as T
WHERE T.Doc in (
SELECT Temp.Doc
FROM
(SELECT Distinct Doc, Status
FROM Tabl1234
WHERE Status in ("PT","PY","CH")
) as Temp
GROUP BY Temp.Doc
HAVING Count(Doc) >=3)

> Ok gurus
>
[quoted text clipped - 28 lines]
>
> Thanks a bunch gurus

Reply to this Message

guaro555 - 28 Aug 2006 20:33 GMT

Yes john.

the only condition to delete the three records is if a doc number has the
three status:

only this records will be deleted

PT 1
PY 1
CH 1

as doc number one has all three status code in the table

Thanks

>Do you need to delete if a doc has 3 status records or must they be three
>different status records?
[quoted text clipped - 29 lines]
>>
>> Thanks a bunch gurus

Reply to this Message