Remove duplicate record - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Remove duplicate record

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-27 : 13:41:03

Hello,
I have a table IDSEQUENCE that only has one field CURRENT_ID.
Usually it only has one row.
For some reason it has two rows which are the same.
How to remove the duplicate row quickly with T-SQL.

Thanks.

heavymind
Posting Yak Master

115 Posts

Posted - 2009-01-27 : 13:51:57

since there is no primary key on your table (according to your description) the easiest way is to store distinct data somewhere , clean up the table and insert the distinct data
select distinct *
into IDseqtemp
from IDSequence
truncate IDSequence
insert IDSequence
select *
from IDseqtemp
drop IDseqtemp

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 13:57:51

[code]Delete t from
(Select Row_Number() over (Partition by CURRENT_ID order by CURRENT_ID )as seq
,* from IDSEQUENCE)t
Where t.seq >1[/code]

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-27 : 14:04:08

Where t.seq >1
or
Where t.seq =1
???

Start from 0 or 1?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 14:05:12

This will give you duplicates.

Select * from
(Select Row_Number() over (Partition by CURRENT_ID order by CURRENT_ID )as seq
,* from IDSEQUENCE)t
Where t.seq >1

Kokkula
Starting Member

41 Posts

Posted - 2009-01-28 : 00:46:01

Hello,

Check this one.

Code:
CREATE TABLE #Temp
(
ID INT
,FName NVARCHAR(50)
,LName NVARCHAR(50)
)

INSERT INTO #Temp VALUES (1,'AAA','BBB')
INSERT INTO #Temp VALUES (1,'AAA','BBB')
INSERT INTO #Temp VALUES (1,'AAA','BBB')
INSERT INTO #Temp VALUES (2,'BBB','CCC')
INSERT INTO #Temp VALUES (2,'BBB','CCC')
INSERT INTO #Temp VALUES (3,'CCC','DDD')

SELECT * FROM #Temp

DECLARE @ID INT,
@FName VARCHAR(50),
@LName VARCHAR(50),
@Count INT

DECLARE Open_Cursor CURSOR FOR
SELECT COUNT (1), ID, FName, LName
FROM #Temp
GROUP BY ID, FName, LNAME
HAVING COUNT(1) > 1

Open Open_Cursor

FETCH NEXT FROM Open_Cursor INTO
@Count
,@ID
,@FName
,@LName

WHILE @@FETCH_STATUS = 0
BEGIN -- 1

SET @Count = @Count-1
SET ROWCOUNT @Count

DELETE FROM #Temp WHERE ID=@ID AND FName = @FName AND LName = @LName

SET ROWCOUNT 0

FETCH NEXT FROM Open_Cursor INTO
@Count
,@ID
,@FName
,@LName

END -- 1

CLOSE Open_Cursor
DEALLOCATE Open_Cursor

SELECT * FROM #Temp

DROP TABLE #Temp

--
Thanks,
Pavan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-28 : 00:50:48

Do not use the cursor solution posted by Kokkula!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kokkula
Starting Member

41 Posts

Posted - 2009-01-28 : 02:14:54

Hello,

Without using Cursor we can do this in the way...

Code:
CREATE TABLE #Temp
(
ID INT
,FName NVARCHAR(50)
,LName NVARCHAR(50)
)

INSERT INTO #Temp VALUES (1,'AAA','BBB')
INSERT INTO #Temp VALUES (1,'AAA','BBB')
INSERT INTO #Temp VALUES (1,'AAA','BBB')
INSERT INTO #Temp VALUES (2,'BBB','CCC')
INSERT INTO #Temp VALUES (2,'BBB','CCC')
INSERT INTO #Temp VALUES (3,'CCC','DDD')

SELECT * FROM #Temp

DELETE T
FROM
(
SELECT Row_Number() Over(Partition By Id, FName, LName Order by Id ) Row
,ID
,Fname
,LName
FROM #Temp
)T
WHERE T.Row > 1

SELECT * FROM #Temp

DROP TABLE #Temp

--
Thanks,
Pavan

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 02:16:30

that's what sodeep has given in the above query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources