MySQL 8 embedded update over several tables   

MySQL update one table embedded select

UPDATE table1 t1,  
(  
    SELECT 'key', MAX(parent) as maxParent  
    FROM table1  
    GROUP BY 'key'  
) t  
SET t1.parent = t.maxParent  
WHERE t1.key = t.key

Updade in Mysql one table with more constraints

UPDATE mytable c1, 
(
SELECT distinct name as name, salary AS maxsalary
FROM mytable
WHERE salary > '0'
) t
SET c1.salary = t.maxsalary
WHERE c1.salary = '0'
AND c1.name = 'Jack' 
AND t.name = c1.name

Updade in Mysql several tables

-- version 1
UPDATE table2 t2,
(   SELECT columt1, key 
    FROM table1 
) t1
SET t2.columt1 = t1.columt1
WHERE t1.key = t2.key

-- version 2
UPDATE table2 t2, (
SELECT 'key', parent
FROM table1) t1 
SET t2.name = t1.name
WHERE t1.id = t2.id

Mysql Updade several tables with join

-- version 1
UPDATE table2 t2,
(   SELECT columt1, key 
    FROM table1 
) t1
SET t2.columt1 = t1.columt1
WHERE t1.key = t2.key

-- version 2
UPDATE table2 t2
JOIN table1 t1 ON t1.key = t2.key 
SET t2.parent = t1.parent;

Previous posts:
Frequent SQL commands select
Frequent SQL commands DML and DDL