Conditional Delete Query

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hi Everyone,

I have a table, and I need the first record where two of
the fields are duplicate, however the order of the delete
will be based on a third field, which is a date, and I
would like to keep the record with the earliest date. I
don't know if this can be done by just sorting the data
before assigning primary keys top a new tanle and copying
it to this??!

Anybody got any answers?

Cheers

Daniel
 
I can give you a head start with a query that will
identify which fields you want to delete but turning that
into a delete query that will actually remove them is
going to be hard.

SELECT Table1.Field1, Table1.Field2, Min(Table1.Field3) AS
MinOfField3
FROM Table1
GROUP BY Table1.Field1, Table1.Field2
HAVING (Count(Table1.Field1))>1 AND (Count(Table1.Field2))
1
Click to expand...

The problem is you can't switch it to Delete since it
involves grouping. You can't link the query back to the
table in a 2nd delete query because now the new query will
involve two sources and won't understand which to delete
records from. You can try using the three fields displayed
from above as a key to get a list of your key values (in
this case 'ID') and make a Delete query where the
condition has the value of IN (SQL sentence).

DELETE Table1.ID
FROM Table1
WHERE Table1.ID In (SELECT Table1.ID
FROM Table1 INNER JOIN Query1 ON (Table1.Field3 =
Query1.MinOfField3) AND (Table1.Field2 = Query1.Field2)
AND (Table1.Field1 = Query1.Field1))

Pretty nasty and slow but the best I can come up with.
Good luck. Maybe someone else can add to this idea.

Cameron Sutherland
 
Back
Top