How to delete redundant data from a table

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

How to delete redundant data from a table
I have a table t1
---------------------
ID Name
---------------------
1 A
2 B
3 C
4 D
3 C
2 B

I want to delete redundant data using sql query.

OUTPUT will be

VB

---------------------
ID      Name
---------------------
1   A
2   B
3   C
4   D

Posted 13-Apr-12 1:48am

P.S Vijay

Add a Solution

Comments

swapnilKumbhar 13-Apr-12 8:37am

Why don't you use primary key while inserting Record?

[no name] 13-Apr-12 8:47am

Or, even better, add a unique constraint on the column 'Name' so that duplicate values can't be inserted into the table in the first place.

2 solutions

Add a Solution

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

Om Prakash Pant · Answer 1 · 2012-04-13T01:52:00

Solution 1

You can use the following SQL query to delete duplicate records:

SQL

DELETE FROM t1 WHERE ID NOT IN (SELECT MAX(ID) FROM t1 GROUP BY [name])

Posted 13-Apr-12 1:52am

Om Prakash Pant

Comments

P.S Vijay 13-Apr-12 7:57am

It is not working

uspatel · Answer 2 · 2012-04-13T01:57:00

Solution 3

There are no. of ways.
I used:
you can make a temporary table with primary key with ID and Name.
and paste these records into temp table.
your temp table now have distict record.
Other:
http://support.microsoft.com/kb/139444[^]
http://www.sql-ex.ru/help/select17.php[^]

Posted 13-Apr-12 1:57am

uspatel

How to delete redundant data from a table

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0

How to delete redundant data from a table

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us