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

Recommended Answers

All 3 Replies

Is this a question or Solution?

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
 )

Thanks i will try it :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.