There is an Index Seek on tblActualFRRouteStopTimes with an output column set. The node immedately to the left of that is a Hash Match on the same table with the same output column set. It is a scan of the index. I don't know why it needs to do that.
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.
(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
By Dan Holmes 725 asked Mar 18, 2016 at 02:12 PM
The question was quite hard to parse, but if I understand you correctly, the answer relates to the slightly convoluted 'logic' query compilation has to apply when trying to match the target of the update statement to the same object referenced in the from clause. This form of
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
The 'logic' then fails to match
The execution plan is only saved from the dreaded cartesian product/unrestricted update due to the predicate
Take away: great care is needed when writing
By SQLkiwi ♦ 6.6k answered Mar 21, 2016 at 12:16 PM