deleting duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

deleting duplicate records

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2011-02-10 : 12:17:44

How do I delete duplicate records in a table, I want to delete all duplicate records that have a commitment history = null

example

Record no shipment no commitment history price
1234 s001 true 999
1234 s001 null 111
1234 s001 null 888
888 s333 null 500
888 s333 true 250
888 s333 null 119

results should be as below when query is executed

Record no shipment no commitment history price
1234 s001 true 999
888 s333 true 250

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-10 : 12:29:09

DELETE A
FROM myTable A
WHERE [Commitment History] IS NULL
AND EXISTS(SELECT * FROM myTable WHERE [Commitment History] IS NOT NULL
AND [Record No]=A.[Record No] AND [Shipment No]=A.[Shipment No])

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-10 : 12:58:50

DELETE A
FROM #tab A
WHERE commitmenthistory IS NULL
AND EXISTS(SELECT Recordno FROM #tab WHERE commitmenthistory IS NULL
AND Recordno=A.Recordno AND shipmentno=A.shipmentno
group by Recordno having COUNT(*)>1)

rob41
Yak Posting Veteran

67 Posts

Posted - 2011-02-10 : 17:07:36

[quote]Originally posted by robvolk

DELETE A
FROM myTable A
WHERE [Commitment History] IS NULL
AND EXISTS(SELECT * FROM myTable WHERE [Commitment History] IS NOT NULL
AND [Record No]=A.[Record No] AND [Shipment No]=A.[Shipment No])
[/quote

I just want it to delete all duplicates from parts database

I was trying to get something like this to run
DELETE FROM [parts]
WHERE CommitHistory IS NULL
AND [Row] > 1

row being > 1 should qualify as a duplicate record

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-10 : 23:25:54

create table #temp
(
Recordno int,
shipmentno varchar(10),
commitmenthistory varchar(10),
price float
)
go

insert into #temp values
(1234,'s001','true',999),
(1234,'s001',NULL,111),
(1234,'s001',NULL,888),
(888,'s333',NULL,500),
(888,'s333','true',250),
(888,'s333',NULL,119)
go

select * from #temp
go

-- Define a CTE with the name "dt"
;WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY Recordno,shipmentno ORDER BY Recordno ) AS 'RowNumber',
*
FROM #temp
where CommitmentHistory is not NULL
)
-- and select the data from the CTE
SELECT
Recordno,shipmentno,commitmenthistory,price
FROM dt
WHERE RowNumber =1

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources