Delete duplicate row

Question

Delete duplicate row

Jpotucek

SSCoach

Points: 18583
More actions
November 18, 2011 at 10:11 am

#252109

I have a table that looks like this:
Create table dbo.OOReportFile (
path VARCHAR(256) not null,
sortOrder int not null,
id int identity,
repname VARCHAR(64) not null,
repfilepath VARCHAR(256) not null,
language_cd CHAR(2) null,
updateddate smalldatetime null,
repFormat VARCHAR(10) not null)
on [PRIMARY] ;
there is a duplicate row in the table:
select * from dbo.OOReportFile where ID = '109'templatedata,1,109,report_CircleOfHonor,resources/COH,en,NULL,PDF
templatedata,1,109,report_CircleOfHonor,resources/COH,en,NULL,PDF
How do I go about deleting one of the duplicate rows?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SQLback SSCrazy Points: 2897 More actions · Answer 1

The only option I can think if is modifying the id column to not have identity and add a new identity column and then delete the row.

SkyBVI SSCoach Points: 15128 More actions · Answer 2

;WITH cte

AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2

--add all column names col1,col2,...

ORDER BY ( SELECT 0)) RN

FROM table)

DELETE FROM cte

WHERE RN > 1

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Jpotucek SSCoach Points: 18583 More actions · Answer 3

not sure what ;WITH cte means..

but I tried this:

Select * (SELECT ROW_NUMBER() OVER (PARTITION BY path, sortOrder,id, repname, repfilepath, language_cd, updateddate, repFormat

--add all column names col1,col2,...

ORDER BY ( SELECT 0)) RN

FROM dbo.OOReportFile)

DELETE FROM cte

WHERE RN > 1

and I can't get the syntax right...

I get this:

Msg 263, Level 16, State 1, Line 1

Must specify table to select from.

Cliff Jones SSChampion Points: 10547 More actions · Answer 4

SKYBVI (11/18/2011)
;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2
--add all column names col1,col2,...
ORDER BY ( SELECT 0)) RN
FROM table)
DELETE FROM cte
WHERE RN > 1
Regards,
Sushant

Yes Sushant, I was just about to post the same suggestion. This is the way to do it.

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

Jpotucek (11/18/2011)
not sure what ;WITH cte means..
but I tried this:
Select * (SELECT ROW_NUMBER() OVER (PARTITION BY path, sortOrder,id, repname, repfilepath, language_cd, updateddate, repFormat
--add all column names col1,col2,...
ORDER BY ( SELECT 0)) RN
FROM dbo.OOReportFile)
DELETE FROM cte
WHERE RN > 1
and I can't get the syntax right...
I get this:
Msg 263, Level 16, State 1, Line 1
Must specify table to select from.

You would have to have SQL 2005 or above for the Common Table Expression:

Create table dbo.OOReportFile (

path VARCHAR(256) not null,

sortOrder int not null,

id int NOT NULL,

repname VARCHAR(64) null,

repfilepath VARCHAR(256) null,

language_cd CHAR(2) null,

updateddate smalldatetime null,

repFormat VARCHAR(10) null)

on [PRIMARY] ;

INSERT INTO OOReportFile (path,sortOrder, id) Values('x',1,1 )

INSERT INTO OOReportFile (path,sortOrder, id) Values('y',2,2 )

INSERT INTO OOReportFile (path,sortOrder, id) Values('z',3,3 )

SELECT * from OOReportFile;

;WITH DupCTE AS

(

Select path, sortOrder, id , ROW_NUMBER()

OVER (PARTITION BY path, sortOrder, id

Order By id) AS RowNumber

From OOReportFile

)

Delete From DupCTE Where RowNumber >1;

SELECT * from OOReportFile;

DROP TABLE OOReportFile

Jpotucek SSCoach Points: 18583 More actions · Answer 6

Thank you!!!

I ran this and it worked:

;WITH cte

AS (SELECT ROW_NUMBER() OVER (PARTITION BY path, sortOrder,id, repname, repfilepath, language_cd, updateddate, repFormat

--add all column names col1,col2,...

ORDER BY ( SELECT 0)) RN

FROM dbo.OOReportFile_TEMP)

DELETE FROM cte

WHERE RN > 1

Can someone explain to me what "cte' means???

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

Jpotucek (11/18/2011)
Thank you!!!
I ran this and it worked:
;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY path, sortOrder,id, repname, repfilepath, language_cd, updateddate, repFormat
--add all column names col1,col2,...
ORDER BY ( SELECT 0)) RN
FROM dbo.OOReportFile_TEMP)
DELETE FROM cte
WHERE RN > 1
Can someone explain to me what "cte' means???

It is a Common Table Expression.

SkyBVI SSCoach Points: 15128 More actions · Answer 8

Cliff Jones (11/18/2011)
SKYBVI (11/18/2011)
;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2
--add all column names col1,col2,...
ORDER BY ( SELECT 0)) RN
FROM table)
DELETE FROM cte
WHERE RN > 1
Regards,
Sushant
Yes Sushant, I was just about to post the same suggestion. This is the way to do it.

Yes cliff, I found cte to be the most useful expressions in sql server until now 🙂

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 9

Jpotucek (11/18/2011)
Thank you!!!
I ran this and it worked:
;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY path, sortOrder,id, repname, repfilepath, language_cd, updateddate, repFormat
--add all column names col1,col2,...
ORDER BY ( SELECT 0)) RN
FROM dbo.OOReportFile_TEMP)
DELETE FROM cte
WHERE RN > 1
Can someone explain to me what "cte' means???

Good tht it worked...as cilff said its common table expression

See these :-

http://www.c-sharpcorner.com/uploadfile/skumaar_mca/common-table-expressioncte-in-sql-server-2005/

http://msdn.microsoft.com/en-us/library/ms190766.aspx

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Cliff Jones SSChampion Points: 10547 More actions · Answer 10

SKYBVI (11/18/2011)
Cliff Jones (11/18/2011)
SKYBVI (11/18/2011)
;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2
--add all column names col1,col2,...
ORDER BY ( SELECT 0)) RN
FROM table)
DELETE FROM cte
WHERE RN > 1
Regards,
Sushant
Yes Sushant, I was just about to post the same suggestion. This is the way to do it.
Yes cliff, I found cte to be the most useful expressions in sql server until now 🙂
Regards,
Sushant

Yes, it is a good tool to have in your SQL Tool-belt.

Jpotucek SSCoach Points: 18583 More actions · Answer 11

Thank you one and all.. time to put on my Quincy lab coat and go do some investigating on 'cte's 😀

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

Jpotucek (11/18/2011)
Thank you one and all.. time to put on my Quincy lab coat and go do some investigating on 'cte's 😀

I remember Quincy, but I am pretty old! 🙂

Jpotucek SSCoach Points: 18583 More actions · Answer 13

Quincy and his trusty assisant Sam Fujiyama to keep him in line 🙂