troubleshooting Question

MS SQL cleaner or simplified

Avatar of Larry Brister
Larry BristerFlag for United States of America asked on
Microsoft SQL ServerRemote AccessSQL
2 Comments1 Solution139 ViewsLast Modified:
My code below works.

It gets the ID and row number
Places  into a #temp table
Gets rit of rn=1
And updates my table.

But... is there a more efficient way to do this?

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp;


SELECT   AccommodationsID ,
         ROW_NUMBER() OVER ( PARTITION BY ProspectID ,
                                          TourID
                             ORDER BY AccommodationsID DESC ) rn
INTO     #temp
FROM     dbo.everyware_t_accommodations
WHERE    User10 <> 'Delete'
ORDER BY AccommodationsID DESC;


DELETE FROM #temp
WHERE rn = 1;

UPDATE a
SET    a.User10 = 'Delete'
FROM   everyware_t_accommodations a
       JOIN #temp t ON a.AccommodationsID = t.AccommodationsID
WHERE  1 = 1;
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