** Edited **
Select from target table
From 13.2.9.8. Subqueries in the FROM section :
Subqueries in the FROM clause can return a scalar, column, row, or table. Subqueries in the FROM clause cannot be correlated subqueries if they are not used in the ON clause of the JOIN operation.
So yes, you can fulfill the above request.
Problem
There are two problems here. There is concurrency, or ensuring that no one else changes the data from under our feet. This is handled with a lock. Work with the actual modification of new and old values is handled by views.
Lock
In the case of your query above, with InnoDB, MySQL first executes SELECT and obtains a separate read lock (shared lock) for each row in the table. If you had a WHERE clause in a SELECT statement, then only the selected records will be locked, where ranges can also block any gaps.
A read lock prohibits any other request from receiving write locks, so records cannot be updated from other sources while they are considered locked.
Then, MySQL gets a record lock (excluding) for each record in the table separately. If there was a WHERE clause in your UPDATE statement, then only the records would be locked, and again, if the WHERE clause selected a range, then your range will be locked.
Any record that had a read lock from a previous SELECT automatically goes into write lock.
A write lock prevents other requests from receiving a read or write lock.
You can use Innotop to see this by running it in lock mode, start the transaction, execute the request (but do not commit it), and you will see the locks in Innotop. Alternatively, you can view parts without Innotop using SHOW ENGINE INNODB STATUS .
Dead ends
Your request is vulnerable to a deadlock if two instances were running at the same time. If request A received a read lock, then request B received a read lock, request A had to wait until the read lock request B was issued before it could receive write locks. However, request B is not going to release read locks until it completes, and it does not complete if it cannot obtain write locks. Request A and request B are at an impasse and therefore at an impasse.
Therefore, you can explicitly lock tables to avoid a huge number of record locks (which use memory and affect performance) and avoid locking.
An alternative approach is to use SELECT ... FOR UPDATE on your inner SELECT. This starts with write locks on all lines, instead of starting with reading and escalating.
Derived tables
For an internal SELECT, MySQL creates a derived temporary table . The output table is the actual non-indexed copy of the data that lives in the temporary table that MySQL automatically creates (unlike the temporary table that you explicitly create and can add indexes to).
Since MySQL uses a view, this is a temporary old value that you refer to in your question. In other words, there is no magic. MySQL does this as if you were doing it somewhere else, with a temporary value.
You can see the view by executing EXPLAIN on your UPDATE statement (supported in MySQL 5.6 +).