Sql-update Statement Multi-table cascading update

When the data table is updated, there may be a situation where the updated content is derived from the other tables, at which point the from is added to the UPDATE statement, and the following is an example:

Update a set a.name=b.name,a.value=b.value from table1 a,table2 b where b.id= ' Id2 ' and a.id=b.id

Then there is a problem, if you update two tables at the same time, can be implemented?

For example, the following statement:

Update set a.name=b.name,a.value=b.value,c.value=fromwhere b.id = ' Id2 '  and a.ID= and a.id=c.id

The result of my attempt is not to update the two tables at the same time, only one table of the table is updated (i.e. the above statement should be split into two pieces, executed separately).

Sql-update Statement Multi-table cascading update