However, if you feel that the status table is ever out of sync due to cancelling, it is easy to refresh with the current value by simply updating it manually with the use an explicit transaction to keep those two tables in sync, yet we do not want to risk having an orphaned transaction if you cancel the process at a point after it has started the transaction but has not committed it.This should be safe to do as long as you don't stop the SQL Agent job. By sending the process a "signal" (similar to in Unix), you can request that it stop at the next convenient moment (i.e. ) and have it clean itself up all nice and tidy-like.
Specifies the temporary named result set or view, also known as common table expression (CTE), defined within the scope of the UPDATE statement.(Bug #11758262, Bug #50439) See Section 184.108.40.206, “Determination of Safe and Unsafe Statements in Binary Logging”, for more information.If you access a column from the table to be updated in an expression, assignments are generally evaluated from left to right.And it doesn't hurt to throw in the current time to have a heart-beat on the process.Given that you want to be able to cancel and restart the process, I am weary of wrapping the the UPDATE of the main table with the UPDATE of the status table in an explicit transaction.