PDA

View Full Version : Deleting double records with SQL

EvE
10-16-02, 09:08
I have an Access-db with approx 5000 records. Many of these records are double and I want to get rid of them. One of those records will have to stay. How can I do that?

Id code opleidingsnaam
1 4406 Accountancy
2 4406 Accountancy
3 4406 Accountancy
r937
10-16-02, 09:15
delete from yourtable
where id not in
( select min(id) from yourtable
group by code )

make a backup copy first, just in case (and also to compare to afterwards, to convince yourself it worked)

rudy
http://rudy.ca/
EvE
10-16-02, 11:54
It does not work. What went wrong?
r937
10-16-02, 12:15
please show the sql, and explain how it didn't work

thanks

rudy
krishan_mahesh
10-22-02, 09:21
Please try it :

Delete from <Table Name> where count(code) > 1

Hope that u will get rid of duplicates.
Thanks.
r937
10-22-02, 10:14
where count(code)>1 is not valid syntax

eve, did you try the query i gave you?


rudy
rnealejr
10-22-02, 13:54
Try the following:

delete from table1 as a
where id not in (select min(id) from table1 as b where a.code= b.code)
Erik_SRJV
08-26-03, 09:25
Originally posted by r937
where count(code)>1 is not valid syntax

eve, did you try the query i gave you?


rudy

should be HAVING count(code)>1
but it deletes without leaving one

the query rnealejr sent works for me; tx!