Use CTE and Table Partition and Ranking Functions to Delete Rows with Duplicates

gistfile1.txt

create procedure dbo.fixDuplication(@accountNumber varchar(255), @productId varchar(30))
as
begin
	WITH CTE as
	(
	select oli.id, oli.serialNumber, row_number() 
	over (partoliion by serialNumber order by serialNumber) as rownumber
	from orderLineItems as oli
	join orders as o on o.id=oli.orderId
	where o.accountnumber = @accountnumber 
		and oli.productId=@productId
		and oli.serialNumber is not null 
	)
	delete oli 
	output deleted.*
	from orderLineoliems as oli
	join CTE as c on c.id=oli.id
	where rownumber>1
end
go


exec dbo.fixDuplication @accountNumber='12345', @productId='IPAD_1234';
[/code]