We help IT Professionals succeed at work.

MS SQL cleaner or simplified

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;

Open in new window

Comment
Watch Question

Database Expert
Awarded 2016
Top Expert 2016
Commented:
This will be better.. I think you will get the same results with this also.

Temp Table is not required. You can use CTE and the update statement.

;WITH CTE AS
(
	SELECT   AccommodationsID ,
			 ROW_NUMBER() OVER ( PARTITION BY ProspectID,TourID ORDER BY AccommodationsID DESC ) rn	
	FROM     dbo.everyware_t_accommodations
	WHERE    User10 <> 'Delete'
)
UPDATE a
SET    a.User10 = 'Delete'
FROM   everyware_t_accommodations a
		INNER JOIN CTE c ON a.AccommodationsID = c.AccommodationsID
WHERE c.rn > 1

Open in new window

Larry Bristersr. Developer

Author

Commented:
Perfect!  Thanks