troubleshooting Question

How can I delete dublicated rows by using for loop in SQL?

Avatar of programmerist
programmeristFlag for Afghanistan asked on
SQLMicrosoft SQL Server
2 Comments1 Solution151 ViewsLast Modified:
Hi; I have a dublicated rows table like below.
	 SELECT count(*) ,d.SapContractNumber, d.MaterialNumber, d.SapDeliveryNumber, d.Batch
                     FROM  DeliveryItems d 
                     GROUP BY
                                 d.SapContractNumber, d.MaterialNumber, d.SapDeliveryNumber, d.Batch HAVING COUNT(*) >1

Above Query Result:
2017-04-25_21-31-18.png
My removing query for dublicated rows below:

      delete from DeliveryItems  where  SapContractNumber = '3120009625' and MaterialNumber = '03002721122' and SapDeliveryNumber = '3140095689' and Batch = '61974301'
 and Id NOT IN
               (SELECT max(d.Id)
                     FROM  DeliveryItems d  where  d.SapContractNumber = '3120009625' and d.MaterialNumber = '03002721122' and d.SapDeliveryNumber = '3140095689' and d.Batch = '61974301'
                     GROUP BY
                            d.SapContractNumber, d.MaterialNumber, d.SapDeliveryNumber, d.Batch
                     )

My Question is here! How can i do that by using above query(First Query)result to remove dublicated rows. I have to use second query inside of the a for loop (FETCH NEXT) to push (SapContractNumber,MaterialNumber ,SapDeliveryNumber,Batch ) from First query.

2017-04-25_21-31-18.png
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros