duplicate cancell

  • Thread starter Thread starter d4h2
  • Start date Start date
D

d4h2

Waht kind of query must i write to this have??

P_ID P_NOME P_ COGNOME
1 ingrid esposito
2 pippo franco
3 ingrid esposito
4 tizio caio
5 ingrid esposito
6 tizio caio
7 pippo franco


delete the duplicate and hold the one with the littel number

P_ID P_NOME P_ COGNOME
1 ingrid esposito
2 pippo franco
4 tizio caio


Excuse me for my english!!
By
Ingrid
 
This is not something that can be performed with a single query.

You can group by the nome and cognome, and get the min of ID.

Select P_NOME, P_COGNOME, Min(P_ID)
GROUP BY P_NOME, P_COGNOME
FROM tablename

This will give you the smallest number, which is the one you want to keep.
After that, you have to find the name combinations that have a higher number
and delete them.
 
Dear d4:

This should do it:

DELETE * FROM d4h2 WHERE ID NOT IN(
SELECT MIN(ID)
FROM d4h2
GROUP BY Nome, Cognome);

Even after 6 years study, my German is probably not equal to your
English. Congratulations!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top