Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting duplicates rows Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 12:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:57 AM
Points: 179, Visits: 448
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

Post #1421949
Posted Wednesday, February 20, 2013 12:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
This should help you out

; WITH cte_Delete_Dups AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY row_id, ename, job, sal ORDER BY row_id ) AS RN, *
FROM empl
)

DELETE
FROM cte_Delete_Dups
WHERE rn > 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/
Post #1421956
Posted Wednesday, February 20, 2013 12:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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'),
('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
Exploring Recursive CTEs by Example Dwain Camps
Post #1421959
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse