Ash Burton

Mobile | Web | Data

Mobile Payments, Android, iOS, Mobile Web, HTML5, Multichannel Customer Service, Web Services, IVR, Business Intelligence, Telecoms, Radio, Photography, Running, Gaming

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 = 
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.