Friday, March 13, 2009

Delete Duplicate Records -- Without having Primary key

-----------Delete Duplicate Records ---------------------------
-------- Without having Primary key ---------------------------
CREATE TABLE [SalesHistory]
( [Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL )
GO
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
SELECT 'Computer','1919-03-18 00:00:00.000',1008.00
UNION ALL
SELECT 'BigScreen','1927-03-18 00:00:00.000',91.00
UNION ALL
SELECT 'PoolTable','1927-04-01 00:00:00.000',139.00
UNION ALL
SELECT 'Computer','1919-03-18 00:00:00.000',1008.00
UNION ALL
SELECT 'BigScreen','1927-03-18 00:00:00.000',91.00
UNION ALL
SELECT 'PoolTable','1927-04-01 00:00:00.000',139.00





set rowcount 1
select 'start'
while @@rowcount > 0
begin

delete a from SalesHistory a
where
(
select count(*) from saleshistory b

where a.product = b.product and a.saledate = b.saledate and a.SalePrice = b.SalePrice)>1
end

set rowcount 0

select product,saledate, SalePrice from SalesHistory

No comments: