UPDATE table SET with multi values

I have spent a lot of time online trying to look for an answer but no success so I decided to post the following. I have the following tables:

TABLE_1
+------------+---------------+
| child_id   | mother_id     |
+------------+---------------+
|          1 |             4 |
|          2 |             3 |
|          3 |             2 |
|          4 |             1 |
+------------+---------------+

TABLE_2
+------------+---------------+
| child_id   | mother_id     |
+------------+---------------+
|          1 |             0 |
|          2 |             0 |
|          3 |             0 |
|          4 |             0 |
+------------+---------------+

I would like to update the values of mother_id in my table 2 with the values of of mother_id located in my table 1. Of course the example above could be solved doing manually:

UPDATE table2 SET mother_id = 1 where child_id = 4;
UPDATE table2 SET mother_id = 2 where child_id = 3;
UPDATE table2 SET mother_id = 3 where child_id = 2;
UPDATE table2 SET mother_id = 4 where child_id = 1;

But let's imagine I have thousand of lines to update. Is there any way to do this using select join queries (on child_id) mixed with update in order to have only a few lines of code?

Thank you.

2 answers

  • answered 2017-11-15 03:12 1000111

    UPDATE
     table_2 AS t2 
     INNER JOIN table_1 AS t1 
       ON t2.child_id = t1.child_id
    SET t2.mother_id = t1.mother_id;
    

    You need to join the two tables based on the matching child_id and later set the values of mother_id from table_1 to that of table_2.

    Here's a demo of it

  • answered 2017-11-15 05:28 Saranya Sridharan

    It is done through the inner join effectively

    update table_2 
    inner join table_1 
    on table_2.child_id = table_1.child_id
    set table_2.mother_id = table_1.mother_id;