SQL SERVER – 2000 – SQL SERVER – Delete Duplicate Records – Rows – Readers Contribution

I am proud on readers of this blog. One of the reader asked asked question on article SQL SERVER – Delete Duplicate Records – Rows and another reader followed up with nice quick answer. Let us read them both together.

Question from Mayank Mishra

Is it possible to delete a single column from a table in SQL 2000.

Solarwinds

Thanks

Answer from Ray McCoy

This works in 2000. WARNING: According to MS, SET ROWCOUNT will not work with INSERT, DELETE, and UPDATE in later versions.

-- Create table with a number of values between zero and nine
SELECT a+b+c AS val
INTO dbo.rmtemp
FROM (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) a
, (SELECT 0 b UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) b
, (SELECT 0 c UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) c
-- Show what you’ve got
SELECT val,COUNT(*) row_count FROM dbo.rmtemp GROUP BY val
-- Limit processing to a single row
SET ROWCOUNT 1
-- While you’ve got duplicates, delete a row
WHILE (SELECT TOP 1 val FROM dbo.rmtemp GROUP BY val HAVING COUNT(*) > 1) IS NOT NULL
DELETE FROM dbo.rmtemp WHERE val IN (SELECT TOP 1 val FROM dbo.rmtemp GROUP BY val HAVING COUNT(*) > 1);
-- Remove single row processing limit
SET ROWCOUNT 0
-- Confirm that only uniques remain
SELECT val,COUNT(*) row_count FROM dbo.rmtemp GROUP BY val
-- Clean up
DROP TABLE dbo.rmtemp
GO

– Comment
I knew there had to be a better way.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER 2005 – Vista Ultimate and SQL Server 2005 DEV Edition
Next Post
SQL SERVER – Find Table in Every Database of SQL Server – Part 2 Extension

Related Posts

5 Comments. Leave new

  • HI pinaldave ,
    i have one query how to delete image duplicate row in my table. my table has no primary key or identity column .. if u have any solution then please help me …

    thanks
    kuldeep.

    Reply
  • i need an answer to following question if can someone help me out with this

    How would you complete the following string, in order to obtain a value equal to
    2 + the current nesting level?
    CREATE PROC usp_NestLevelValues AS
    SELECT @@NESTLEVEL AS ‘Current Nest Level’;
    EXEC __________ N’SELECT @@NESTLEVEL as TwoGreater’ ;
    GO
    EXEC usp_NestLevelValues;
    GO

    Reply
  • This is your answer
    CREATE PROC usp_NestLevelValues AS
    SELECT @@NESTLEVEL AS ‘Current Nest Level’;
    EXEC sp_executesql N’SELECT @@NESTLEVEL as TwoGreater’ ;
    GO
    EXEC usp_NestLevelValues;
    GO

    Reply
  • Ah, great post. And I am sooo happy that the SQL 2005-2008 introduced the CTEs and the ranking functions! :) By using both you can easily remove duplicate rows.

    Reply
  • Hi,
    Creating table copy with unique clustered index on ID column, and coping
    original table rows to table copy sounds reasonable , specially for large tables. It works fast !!! using SQL Server in-built unique clustred key constraint with eliminate duplicates with IGNORE_DUP_KEY option enabled.

    For instance:

    CREATE TABLE Data_backup….. (the same structure as original table Data with duplicates)

    (SQL Server 2000)
    CREATE UNIQUE CLUSTERED INDEX PK_Data_backup
    ON Data_backup(ID)
    WITH IGNORE_DUP_KEY, FILLFACTOR = 90
    GO

    SET IDENTITY_INSERT Data_backup ON
    INSERT INTO Dane_backup(ID,data1,data2)
    SELECT * from Data
    SET IDENTITY_INSERT Data_backup OFF

    Finally you will get table Data_backup with duplicates removed automatically by unique clustered key contraint mechanism.

    Krzsiek. IT WORKS FAST FOR LARGE TABLES.

    :)

    Reply

Leave a Reply

Menu