Trying to extract a value from a derived table's CROSS APPLY, and use it in the outer query's OUTPUT INTO, but getting an error

Question

I have this db fiddle showing the situation, I'm trying to get the value of OpenTimeCalc.OpenTime from the derived table and use it in the OUTPUT INTO of the outer query like you can see in the stored procedure but I get this error:

The column reference "inserted.OpenTimeResult" is not allowed because it refers to a base table that is not being modified in this statement.

But it is being modified since it is in the SET part of the UPDATE, and I have no idea on how to solve it. Why is this happening?

You might see a lot of lines in the Seed data line 10 but it's just a timezone mapper, you can fold the Declare statement and it will just be a couple lines.

The error happens on the line 22 of the stored procedure in the OUTPUT statement. I've put DELETED.OpenTimeResult but it should be INSERTED.OpenTimeResult but then I get the error.

Answer 1

This is an undocumented restriction, which I discovered while working on one of your previous questions. See my question on it on the Database Adminsitrators site.

When you refer to the table that is actually being modified in the UPDATE, you must use either inserted or deleted. This is documented here.

But in your case you have an updatable derived subquery which you are modifying, so any columns which are calculated, or come from other tables, or for that matter in my case a window function, must use deleted not inserted.

It doesn't matter either way, because the column isn't being modified in the SET part anyway: you can't modify a calculated column that doesn't actually exist.

To clarify: inserted refers to the data after the SET and deleted refers to the before the SET, but after any other calculations on it using joins, APPLY or whatever.