February 20, 2013 at 12:31 am
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
February 20, 2013 at 12:48 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 20, 2013 at 12:54 am
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
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy