Deleting Duplicate Rows

--author: barkın ünüulu

--**************************************
-- Name: Deleting Duplicate Rows
-- Description:The Purpose of this code
-- is to delete the duplicate values occuring in a table. This code is column-orien
-- ted. That means the code works for the duplicate values occuring in a specified
-- column and deletes the values of the rows corresponding to that column.
-- By: barkın ünüulu
--
-- Inputs:I am going to specify the column name as COLUMNNAME and table name as
-- TABLENAME. It should be noted that the developers that are willing to run this c
--ode should put an "id" column in to their tables, which increments automatically
-- Side Effects:As the row number increa
-- ses, the time elapsed for the code increases...
--
--**************************************

DECLARE @i int
DECLARE @j int
DECLARE @k int
SET @k=(select count(*) FROM TABLENAME)
SET @i=1
WHILE @i<=@k
BEGIN
SET @j=@i+1
WHILE @j<=@k
BEGIN
IF ((select COLUMNNAME FROM TABLENAME WHERE ID=@i)=
(select COLUMNNAME FROM TABLENAME WHERE ID=@j))
begin
DELETE FROM TABLENAME
WHERE ID=@j
end
SET @j=@j+1
end
SET @i=@i+1
END

SQL Cursor Example

author: Robert Wawszkiewicz

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = 'UT'
ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Books by Author: ' +
@au_fname + ' ' + @au_lname PRINT @message -- Declare an inner cursor based
-- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0
PRINT ' <<No Books>>' WHILE @@FETCH_STATUS = 0
BEGIN SELECT @message = ' ' + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title END CLOSE titles_cursor
DEALLOCATE titles_cursor -- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
GO