Eduardo Salomon Raydan Eduardo Salomon Raydan - 8 months ago 27
MySQL Question

Update table from another table with a condition on a common column

I'm new to Mysql. I have a parent table with up to 3 children, I would like to update only the the first child from another child table with a condition.

Table

TblChild


id_child - parent_id - child_name - child_number


Table
TblParent


Id_paretn - parent_name - child_name_1 - child_id_1 - child_name_2 - child_id_2 - child_name_3 - child_id_3


Query

UPDATE TblParent
SET TblParent.child_name_1 = TblChild.child_name,
TblParent.child_id_1 = TblChild.child_id
FROM TblChild, TblParent
WHERE TblParent.id_parent = TblChild.parent_id
AND TblChild.child_number = '1';


Thank you for your time.

Answer Source

In mysql you could use a update join

UPDATE TblParent
INNER JOIN TblChild ON  ( TblParent.id_parent = TblChild.parent_id 
                            AND TblChild.child_number = '1')
SET 
TblParent.child_name_1 = TblChild.child_name, 
TblParent.child_id_1 = TblChild.child_id