Why the HASH JOIN on the same table in sequence?
The SQL isn't that complicated but the UPDATE joins back to the CTE on which this is based. When i removed the CTE and made the SQL in the following form the extra JOIN is removed. Why does that make a difference? The engine should have everthing thing it needed from the index seek.
UPDATE sa-- dbo.tblActualFRRouteStopTimes SET DepartCompleted = 1, DepartInputID = CASE sa.DepartCompleted WHEN 0 THEN @FR_INPUT_TYPE_BACKCLEAR_MAX_AGE ELSE sa.DepartInputID END, ArriveCompleted = 1, ArrivalInputID = CASE sa.ArriveCompleted WHEN 0 THEN @FR_INPUT_TYPE_BACKCLEAR_MAX_AGE ELSE sa.ArrivalInputID END FROM dbo.tblActualFRRouteStopTimes sa JOIN dbo.tblActualFRTrips ta ON sa.DailyTripID = ta.DailyTripID JOIN dbo.tblActualFRRuns ra ON ra.DailyRunID = ta.DailyRunID
(edit)
I should also mention that changing the SELECT in the CTE to the below so that it has the requisite columns doesn't help. The extra join still exists.
WITH old_stops AS(
SELECT sa.DailyTimetableID, sa.DepartCompleted, sa.ArriveCompleted, sa.DepartInputID, sa.ArrivalInputID
The simplest for the logic to handle is when the update target is an alias (i.e. 'sa' in the rewrite) where the aliased table is clearly present in the from clause. When you use a CTE, you cannot write UPDATE sa directly because that alias is hidden inside the scope of the CTE.
The 'logic' then fails to match dbo.tblActualFRRouteStopTimes sa in the CTE to the UPDATE dbo.tblActualFRRouteStopTimes reference, so the plan ends up touching the table twice.
The execution plan is only saved from the dreaded cartesian product/unrestricted update due to the predicate WHERE DailyTimetableID IN (SELECT DailyTimetableID FROM old_stops);. Nevertheless, it is inefficient.
Take away: great care is needed when writing UPDATE … FROM statements to avoid unintended consequences, incorrect results, and/or poor performance. Use an alias as the target whenever possible (= always, for all practical purposes).