How to delete duplicate record in sql server without using procedure and cursor?

Question

create table sample(id int,names varchar(50))

insert into sample values(1,'Arun')
insert into sample values(1,'Arun')
insert into sample values(2,'Javith')
insert into sample values(2,'Javith')
insert into sample values(3,'Simma')
insert into sample values(3,'Simma')
insert into sample values(4,'Sri')
insert into sample values(4,'Sri')
insert into sample values(5,'Vijay')
insert into sample values(5,'Vijay')


(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)


select * from sample

1 Arun
1 Arun
2 Javith
2 Javith
3 Simma
3 Simma
4 Sri
4 Sri
5 Vijay
5 Vijay

Answer 1

The complicated section about duplicate eliminating is protecting in simple terms between the duplicates at the same time as there is not any longer a fashion to tell apart between them. between the a lot less mistakes-companies thanks to finish that is to make it so that you could tell them aside: upload a column to the table and make it autonumber or something similar. Then, you could create a normal technique alongside the lines of CREATE CURSOR delete_targets AS choose id, fname, lname, MIN(newField) AS keepValue FROM someTable crew with the help of id, fname, lname HAVING count number(*) > a million Then, fetch each and every row of the delete_targets into @id, @fname, @lname and @keepValue and then: DELETE FROM someTable the position id = @id AND fname = @fname AND lname = @lname AND newField > @keepValue once you're performed, drop the hot column.

Answer 2

Getting rid of all records with duplicates is fairly simple:



DELETE FROM sample WHERE id IN

(SELECT id FROM sample GROUP BY id HAVING COUNT(*) > 1)



Getting rid of all dups except 1 is trickier - if you're trying to do it with a single query rather than a procedure or cursor, I can't think of any method offhand...every way I can think of would at least involve a mutlistep process - either a) using cursors, b) adding an autonumber column to give uniqueness, deleting and then dropping the column or c) copying one dup to a temp table, deleting all dups from main table and reinserting from temp table.

Answer 3

The complicated section about duplicate eliminating is protecting in simple terms between the duplicates at the same time as there is not any longer a fashion to tell apart between them. between the a lot less mistakes-companies thanks to finish that is to make it so that you could tell them aside: upload a column to the table and make it autonumber or something similar. Then, you could create a normal technique alongside the lines of CREATE CURSOR delete_targets AS choose id, fname, lname, MIN(newField) AS keepValue FROM someTable crew with the help of id, fname, lname HAVING count number(*) > a million Then, fetch each and every row of the delete_targets into @id, @fname, @lname and @keepValue and then: DELETE FROM someTable the position id = @id AND fname = @fname AND lname = @lname AND newField > @keepValue once you're performed, drop the hot column.

Answer 4

If you can't write a procedure to detect and delete duplicate data then your only other option is to prevent the data from being duplicated in the first place. Try using the number field as a unique field in your table. That should prevent the duplicate records from being written.

Answer 5

jai matadi dinesh bro ..



well as a web developer u should think as a user ..



here u mentioned you want to delete the duplicate values .. but instead you should try to give the user a message when the user trying to enter a duplicate code something the message will be returned like "sorry .. the value already exists" etc .. .. find out the code in whatever language u r working with PHP,ASP.NET or etc ..



hope this helps as a web developer . .



JAI MATADI ..



mitt

Answer 6

Here are some ways to do this

http://sql-ex.ru/help/select17.php