February 27, 2009 at 1:25 am
Hi,
I can only get this done with a cursor.... can anyone help to get it done without a cursor?
I have a table with telephone numbers, and I want to delete rows so that each telephone number only occurs once.
Besides the telephone number (field ltel) there's also a status field lstatus; when 0 the telephone number has never been called yet, when it is 1 the telephone number has been called before.
When lstatus=1, the row must never be deleted (even if it is duplicate).
For the table below, the following must be the result:
1,0,'111-1111111' -> stays, because 111-1111111 occurs only once
2,1,'222-2222222' -> stays, because lstatus=1
3,1,'333-3333333' -> stays, because lstatus=1
4,0,'333-3333333' -> deleted, because lstatus=0 and 333-3333333 occurs more than once
5,1,'444-4444444' -> stays, because lstatus=1
6,1,'444-4444444' -> stays, because lstatus=1
7,0,'444-4444444' -> deleted, because lstatus=0 and 444-4444444 occurs more than once
8,0,'555-5555555' ; 9,0,'555-5555555' -> one of these is deleted (doesn't matter which), the other stays
DECLARE@list TABLE (lpid INT, lstatus TINYINT, ltel CHAR(11) )
INSERT INTO @list SELECT 1,0,'111-1111111'
INSERT INTO @list SELECT 2,1,'222-2222222'
INSERT INTO @list SELECT 3,1,'333-3333333'
INSERT INTO @list SELECT 4,0,'333-3333333'
INSERT INTO @list SELECT 5,1,'444-4444444'
INSERT INTO @list SELECT 6,1,'444-4444444'
INSERT INTO @list SELECT 7,0,'444-4444444'
INSERT INTO @list SELECT 8,0,'555-5555555'
INSERT INTO @list SELECT 9,0,'555-5555555'
SELECT * FROM @list
Thanks!
Ray
February 27, 2009 at 1:34 am
Try this
--select
;WITH CTE AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY Ltel ORDER BY lstatus desc) AS SEQ FROM @list
)
SELECT *
FROM cte
WHERE LSTATUS =0
and seq>1
--delete
;WITH CTE AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY Ltel ORDER BY lstatus desc) AS SEQ FROM @list
)
delete
FROM cte
WHERE LSTATUS =0
and seq>1
--
select * from @list
Also see -
how to delete the duplicate Rows
John Smith
February 27, 2009 at 1:37 am
Many ways to delete the duplicate records, for more information navigate the following URL's:
http://www.sqlservercentral.com/articles/Advanced+Querying/deletingduplicaterecords/1630/ --> with cursors
http://www.sqlservercentral.com/scripts/TSQL/62866/
http://www.sqlservercentral.com/scripts/Miscellaneous/65420/
http://www.sqlservercentral.com/scripts/duplicate+records/65099/
February 27, 2009 at 1:45 am
Wow..... fabulous forum, don't even have the time to get a cup of coffee before answers come in.
Thanks for the replies! The CTE solution is magic to me, but it works well, so I'll dive into it to see how it works.
Thanks again
February 27, 2009 at 4:40 am
Hi I have a small example for Duplicate avoids Ok
...
Declare @id int,@name varchar (50),@cnt int,@roll_no numeric
Declare getall cursor local static For
Select count (1), id, name, roll_no
from t_name1 (nolock)
group by id, name,roll_no having count(1)>1
Open getall
Fetch next from getall into @cnt,@id,@name,@roll_no
While @@fetch_status=0
Begin
Set @cnt= @cnt-1
Set rowcount @cnt
Delete from t_name where id=@id and name=@name
and salary=@roll_no
Set rowcount 0
Fetch next from getall into @cnt,@id,@name,@roll_no
End
Close getall
Deallocate getall
Reply Must
February 27, 2009 at 4:58 am
Thanks for the reply. But, as I started with, I don't want to use cursors if that can be avoided. Mangal Pardeshi's reply works very well and very fast I might add.
Ray
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply