How can I delete Duplicate Records from a table

[sbaud2003]

Hello Sir/Madam

Please help me to deleting duplicate records records from a Table
I tried but it deletes all the recordes with the key, i am trying.

 

[theDBguy]

You can create a duplicate table structure and set a unique index on the duplicate field(s). Then, insert the data from the original table.

 

[The_Doc_Man]

The suggestion from theDBguy should work OK. The problem was that your key, whatever it was, was not unique. For future reference, the exact problem you just had is the reason why you need unique keys in tables. When you need selectivity, having only keys that can contain duplicates tends to limit your options.

 

[ebs17]

If you only want to delete parts of them, you need an attribute that allows you to distinguish between the records and can therefore be used to determine which records you want to delete or which records you want to keep. A typical field would be an ID with a continuous number (autoincrement).

DELETE
FROM
   TableX AS T
WHERE
   EXISTS
      (
         SELECT
            NULL
         FROM
            TableX AS X
         WHERE
            X.DuplicateKey = T.DuplicateKey
               AND
            X.ID < T.ID
      )

Here the highest ID per duplicate key should remain.

 

[ebs17]

@theDBguy 's solution is the simplest.

But you should also be able to do a delete query on a table, it's not a big challenge.
If you always need a new table to display results, that's not beneficial.

 

[ebs17]

OK, you have a query that produces 5 duplicates.

That's not OK. A query that produces unwanted duplicates should be reworded. This is about a table that contains duplicates, for whatever reason.

Not so easy if you don't already have a unique PK you can use to isolate ONE row.

The ID with autonumber was just one possible example to differentiate records. Maybe there are already existing usable fields such as a timestamp field, you just have to look at the entire table to see what there is when it is fully displayed.

If there is no suitable criterion for differentiation and therefore no primary key, you can simply create such a autonumber field in the table using DDL.

Of course, after cleaning up unwanted duplicates will be avoided by using a unique index. Please don't say SQL is difficult. In the end, someone still believes that. SQL is the native language in a database. If you consciously refuse to do this, you are moving in the wrong environment.

 

[ebs17]

I think you missed the point of the question.

This is what I call an exclusive opinion. I can only shake my head at what some people think.

help me to deleting duplicate records records from a Table

For those who can read: My suggestion corresponds to the wording of the question.
Or should I understand the counter-discussion to mean that it is a huge effort for those involved to write a delete query?
As @The_Doc_Man likes to write: You can have more tools than just a hammer.
None of us know the exact situation that exists. It makes little sense to interpret something appropriate yourself.