CODEWITHSUNDEEP
sqlms-access
Gold
Gold

Reputation: 62454

delete duplicate rows from my table

i need to delete all duplicate rows in my table - but leave only one row

MyTbl
====
Code     |  ID  |  Place  |  Qty  |  User
========================================
1        |  22 |   44     |  34   |  333
2        |  22 |   44     |  34   |  333
3        |  22 |   55     |  34   |  333
4        |  22 |   44     |  34   |  666
5        |  33 |   77     |  12   |  999
6        | 44  |   11     |  87   |  333
7        | 33  |   77     |  12   |  999

i need to see this:

Code    | ID  |  Place  |  Qty  |  User
=======================================
1       | 22 |   44     |  34   |  333
3       | 22 |   55     |  34   |  333
4       | 22 |   44     |  34   |  666
5       | 33 |   77     |  12   |  999
6       | 44 |   11     |  87   |  333

Upvotes: 0

Views: 73

Answers (2)

vinoth_S
vinoth_S

Reputation: 27

Try this,

 WITH CTEMyTbl (A,duplicateRecCount)
    AS
    (  
    SELECT id,ROW_NUMBER() OVER(PARTITION by id,place,qty,us  ORDER BY id)
    AS duplicateRecCount   
    FROM MyTbl
    )
    DELETE FROM CTEMyTbl
    WHERE duplicateRecCount > 1

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

In most databases, the fastest way to do this is:

select distinct t.*
into saved
from mytbl;

delete from mytbl;

insert into mytbl
    select *
    from saved;

The above syntax should work in Access. Other databases would use truncate table instead of delete.

Upvotes: 2

Related Questions