Update a Table Using a Join in MySQL
It's one of those occasional requirements that come up in a DBA or Data Warehouse architects's life, to update one table with values from another. It's a fairly straight-forward syntax in MySQL but one that differs from my 'platform of choice' Microsoft SQL Server, the syntax goes...
UPDATE child c INNER JOIN parent p ON p.id_parent = c.id_parent SET c.parent_name = p.name WHERE c.parent_name IS NULL AND p.legal_guardian = 1;
Now my example is fairly contrived but it does show not only updating the table over the join but also using criteria in the WHERE clause, there's also an advantage over T-SQL in that you can use the table alias in all cases.