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.
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)
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.
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
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
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.
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.
:)