duplicate records delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

duplicate records delete

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-02-19 : 05:24:50

How to delete the duplicate record? (duplicate record created when the same insert repeated twice or thrice due to mistake or somereason)

Need the script to delete duplicate records, after removing duplicate record, the given table should have 3 records (viswa,katti,vijay)

Script to have such table

-------------------------

if object_id('viswa') is not null

drop table viswa

go

create table viswa

(

empid int,

empname char(10),

empsalary int

)

insert into viswa values(1,'viswa',100000)

insert into viswa values(1,'viswa',100000)

insert into viswa values(1,'viswa',100000)

insert into viswa values(1,'viswa',100000)

insert into viswa values(1,'viswa',100000)

insert into viswa values(1,'viswa',100000)

insert into viswa values(2,'vijay',200000)

insert into viswa values(3,'katti',300000)

insert into viswa values(1,'viswa',100000)

insert into viswa values(2,'vijay',200000)

insert into viswa values(1,'viswa',100000)

please help on this...

Sachin.Nand

2937 Posts

Posted - 2010-02-19 : 05:48:18

If using SQL 2005
Delete t from(select ROW_NUMBER()over(partition by empid,empsalary order by empid,empsalary)as rowid from viswa)t where rowid>1

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 05:59:32

http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2010-02-19 : 06:21:38

Try below code




STEP 1

	SELECT empid, empname, col3=count(*)
	INTO Dup_STG
	FROM viswa
	GROUP BY empid, empname
	HAVING count(*) > 1

STEP 2

	SELECT DISTINCT viswa.*
	INTO DUP_FINAL
	FROM viswa, Dup_STG
	WHERE viswa.empid = Dup_STG.empid
	AND viswa.empname = Dup_STG.empname

STEP 3

	SELECT empid, empname, count(*)
	FROM DUP_FINAL
	GROUP BY empid, empname

STEP 4

	DELETE viswa
	FROM viswa, Dup_STG
	WHERE viswa.empid = Dup_STG.empid
	AND viswa.empname = Dup_STG.empname

STEP 5

	INSERT viswa SELECT * FROM DUP_FINAL

STEP 6

	SELECT * FROM VISWA

-------------------------
[R][A][J]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 06:23:49

quote:

Originally posted by rajdaksha

Try below code




STEP 1

	SELECT empid, empname, col3=count(*)
	INTO Dup_STG
	FROM viswa
	GROUP BY empid, empname
	HAVING count(*) > 1

STEP 2

	SELECT DISTINCT viswa.*
	INTO DUP_FINAL
	FROM viswa, Dup_STG
	WHERE viswa.empid = Dup_STG.empid
	AND viswa.empname = Dup_STG.empname

STEP 3

	SELECT empid, empname, count(*)
	FROM DUP_FINAL
	GROUP BY empid, empname

STEP 4

	DELETE viswa
	FROM viswa, Dup_STG
	WHERE viswa.empid = Dup_STG.empid
	AND viswa.empname = Dup_STG.empname

STEP 5

	INSERT viswa SELECT * FROM DUP_FINAL

STEP 6

	SELECT * FROM VISWA

-------------------------
[R][A][J]

Is this method easier than what Idera posted?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2010-02-19 : 06:29:51

Hi

I agree..Idera OP is fine..

Mine is Compatibility for lower version to higher version in SQL SERVER

-------------------------
[R][A][J]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-19 : 06:41:41

For sql server 2005
this query will also work

WITH CTE AS
(
SELECT ROW_NUMBER() OVER ( Partition by empid, empname, empsalary order by empid) as rno FROM viswa
)
DELETE FROM cte WHERE rno <> 1

Vabhav T

Sachin.Nand

2937 Posts

Posted - 2010-02-19 : 06:58:17

With the current sample data provided & if using SQL 2000 u will have to put a identity column as id in the table the following quey will work



delete from viswa where id not in
(
		select min(id) from viswa group by empid,empsalary,empname
)

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources