Deleting duplicates rows

Question

Post reply

Deleting duplicates rows

raghuldrag

Hall of Fame

Points: 3160
More actions
February 20, 2013 at 12:31 am

#268888

hai friends,
i m created one table ,i wanna delete the duplicate values
create table empl
(
row_id int,
ename varchar(120),
job varchar(120),
sal varchar(100)
)
insert into empl ('4','ram','IT','60000')
insert into empl ('4','ram','IT','60000')
insert into empl ('4','ram','IT','60000')
insert into empl ('4','ram','IT','60000')
i am write the query to delete duplicates
delete from empl t
where t.ename>(select min(t1.ename) from empl t2 where t.job=t2.job and t.sal=t2.sal)
but its was showimg error "Incorrect syntax near 't'." like dis do the need full

Viewing 3 posts - 1 through 2 (of 2 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

This should help you out

; WITH cte_Delete_Dups AS

(

SELECTROW_NUMBER() OVER ( PARTITION BY row_id, ename, job, sal ORDER BY row_id ) AS RN, *

FROMempl

)

DELETE

FROMcte_Delete_Dups

WHERErn > 1

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

DROP table #empl

CREATE TABLE #empl

(

row_id int,

ename varchar(120),

job varchar(120),

sal varchar(100)

)

INSERT #empl VALUES

('4','ram','IT','60000'),

('4','ram','IT','60000');

WITH OrderedData AS (

SELECT row_id, ename, job, sal,

rn = ROW_NUMBER() OVER(PARTITION BY row_id, ename, job, sal ORDER BY row_id, ename, job, sal)

FROM #empl

) DELETE OrderedData WHERE rn > 1

SELECT * FROM #empl

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden