DELETE QUERY

[guaro555 via AccessMonster.com]

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

 

[Guest]

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'));

 

[John Spencer]

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)

 

[guaro555 via AccessMonster.com]

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,

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

 

[guaro555 via AccessMonster.com]

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?

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