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.

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)