what you are asking about is called referential integrity. there are different ways of handling it ,by the various DB engine implementations. The major DBs lag many years behind current SQL standard, and in any case, the SQL standard is a bit contentious etc..and in the end vendors try to come up with the goods in various ways.
declarative referential integrity constraints are the most strict/rigid, and you always know exactly what happens when an anomally happens. But the typical implementation of many DB engines is through the use (and abuse) of triggers. triggers are database events (such as update to a table) that you can assign code to. this is very shonky though, as you have to write own code to do whatever you want when an update to a particular table column occurs. however if rigour is applied, you could theoretically end up with something implementing declarative referential integrity through the use of triggers. mind you, you have code (triggers) hanging off here and there and everywhere..and referential integrity is really a basic premise of a relational database. some DB engines even require you to set up triggers just to set up basic table relationships, as they treat each table as basically an independent entity.
back to your question...what happens is if there are referential integrity constraints of any sort on the tables concerned, then these apply; otherwise if there's no table relationship & referential integrity constraints whatsoever you end up updating exactly what matched, in an atomic (all happens at once) way. so the UPDATE statement (if constraints are absent) would affect the same rows that you would changing it into a SELECT statement.