0

today i came across to an sql problem from one of the threads in the database forum :
to solve the problem i created a table named "names" having these columns :
PKID | name | lastname

and i entered some rows having the same values for name and the last name. My aim was to delete all the rows where name and lastname values are both duplicate. The sql statement is as follows :

declare @name nvarchar(50)
declare @lastname nvarchar(50)
declare myfirstcursor cursor for
select name,lastname
from names
group by name,lastname
having count(*) > 1

open myfirstcursor

FETCH NEXT FROM myfirstcursor INTO @name, @lastname
WHILE @@FETCH_STATUS = 0
begin

delete from names where (name=@name and lastname=@lastname)

FETCH NEXT FROM myfirstcursor INTO @name, @lastname
end

close myfirstcursor
deallocate myfirstcursor

3
Contributors
3
Replies
4
Views
9 Years
Discussion Span
Last Post by serkan sendur
0

Fee Fi Fo Fum I smell the blood of a Tsql Cursor, be he slow or be he slower, I'll grind his bones with my set theory mower.

Sorry I'm on a personal crusade against cursors :)

I offer my own humble solution:

DELETE FROM names
WHERE
 EXISTS
 (
 SELECT
  PKID
 FROM
  names Dupnames
 WHERE
  Dupnames.name  = names.name
  AND Dupnames.lastname = names.lastname 
  AND Dupnames.PKID < names.PKID
 )
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.