How to delete duplicate values and keep single row

Question

1.00/5 (1 vote)

See more:

hi all

One of my table entries duplicated . now my table contains same row twice
for eg

code  name   .........

ab    Amritha ........
ab    Amritha ........
ac    Tintu .......
ac    Tintu .......

Problem is My table without primary key . i want a delete query to delete duplicated values finally need table as below

code  name    ......
ad    Amritha ........
ac    Tintu   .........

How to do this??

Thanks in Advance
Amritha

Posted 31-Aug-13 18:00pm

amritha444

Updated 31-Aug-13 18:02pm

v4

Add a Solution

Comments

Sergey Alexandrovich Kryukov 1-Sep-13 0:27am

Why not keeping records unique in first place? Then you would not need to remove duplicates.
Think about it: are you going to have your database inconsistent for a while, before you remove those duplicates?
—SA

amritha444 1-Sep-13 0:38am

thnks Sergey Alexandrovich Kryukov for the response
i knw importance of keeping unique row .this is existing live db cant make such changes now. through front value never duplicate.problem is that i exported table twice . im searching for a delete query to do this.

RedDk 1-Sep-13 14:00pm

Perhaps achieving an appropriate end might be done by simple CREATE TABLE using [Idx][int]IDENTITY(1,1) plus all "old" fields using the "old" data, along with it's duplicates, and SELECT DISTINCT at an xfer query?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Answer 1 · 2013-09-01T16:17:00

Hi,
Check this links.This will solve your question

SQL

SQL> DELETE FROM table_name A WHERE ROWID > (
     SELECT min(rowid) FROM table_name B
     WHERE A.key_values = B.key_values);

http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table[^]

Remove Duplicate Rows from a Table in SQL Server[^]