How can i delete duplicate rows from the table

[chetankumar_ramanuj@yahoo]

How can i delete duplicate rows from the table

Note: If the values in all the fields of a table is same

 

[chetankumar_ramanuj@yahoo]

just add a new field with identity type and now go to sql enterprise manager -> open that table -> select that duplicate and delete those.

 

[chetannramanuj]

add identity column in a table, then select rows in sql enterprise manager and delete those duplicate rows

there is nothing to with this matter.
just open tabel in designe view and add identity column manually,
then save it and close it.
open all rows.
select those duplicate rows.
delete thos duplicate rows.
just simple yaar.

 

[chetannramanuj]

there is nothing to with this matter.
just open tabel in designe view and add identity column manually,
then save it and close it.
open all rows.
select those duplicate rows.
delete thos duplicate rows.
just simple yaar.

there is also another way.
by querying using MAX and MIN functions you can delete those duplicate rows, but the condition is that the table must contain atleast one numeric field.

 

[Stonkie]

This is just a path you could try and I have not tested it, but I think you could use an SQL Query such as this one :

DELETE myTable WHERE Id NOT IN (SELECT Id FROM myTable GROUP BY col1, col2, etc)

I assume that Id is your primary key and changes between records. If you do not have a primary key, you'll have to create one.

 

[cjard]

there is also another way.
by querying using MAX and MIN functions you can delete those duplicate rows, but the condition is that the table must contain atleast one numeric field.

I cant see why; you can ask for the MAX of a varchar column..

This is just a path you could try and I have not tested it, but I think you could use an SQL Query such as this one :

DELETE myTable WHERE Id NOT IN (SELECT Id FROM myTable GROUP BY col1, col2, etc)

I assume that Id is your primary key and changes between records. If you do not have a primary key, you'll have to create one.

Actually, that wont delete anything, i'm afraid. Plus, you wont be able to select an ID field but group by other fields..

 

[cjard]

3 step process:

SELECT DISTINCT * INTO no_dupes FROM original_table
DELETE FROM original_table
INSERT INTO original_table SELECT * FROM no_dupes

And then for goodness sake put a primary key on that table so the problem doesnt come again!

 

[Stonkie]

DELETE myTable WHERE Id NOT IN (SELECT MAX(Id) FROM myTable GROUP BY col1, col2, etc)

I forgot the MAX function which tells to keep only the last one of those with the same values for the columns in the GROUP BY (you can set the columns in the GROUP BY which are significant in the comparison of duplicates). Obviously, you need the Id column to be your primary key and be different for every record, even between duplicates (if you don't have a primary key, it is good practice to add one to every one of your tables), otherwise, I guess it should work fine. I may be missing something...

 

[cjard]

I would also, in most cases, seriously avoid using the IN operator because oif the truly horrendous performance implications it carries. In one off queries it ought to be fine, but i'd never use it in regular queries for in-lists longer than you would be prepared to type by hand

Additionally, the inference the OP gives us is that this table does not have an id, or any column capable of uniquely identifying a row.. he simply states that all columns must be the same for the row to be considered a duplicate:

Note: If the values in all the fields of a table is same

In this case, you must use distinct (or group by every column which is the same as distinct, but more tedious to type) because there is no ID column (which will be different for each row thereby making this table have no duplicates, because at least one column is different - the OP tells us that there are rows where all columns are the same).

In situations where there is an ID column, and de-duping is being done on a subset of columns, I would wholeheartedly recommend your approach, perhaps with a caution of not using IN if the number of duplicates are likely to be in the thousands