MySQL update table based on another tables value

Question

I have a two tables,

Here is my first table,

ID      SUBST_ID        CREATED_ID 1       031938          TEST123 2       930111          COOL123 3       000391          THIS109 4       039301          BRO1011 5       123456          COOL938 ...     ...             ... 

This is my second table,

ID      SERIAL_ID       BRANCH_ID 1       039301          NULL 2       000391          NULL 3       123456          NULL ...     ...             ... 

I need to some how update all rows within my second table using data from my first table.

It would need to do this all in one update query.

Both SUBST_ID and SERIAL_ID match, it needs to grab the created_id from the first table and insert it into the second table.

So the second table would become the following,

ID      SERIAL_ID       BRANCH_ID 1       039301          BRO1011 2       000391          THIS109 3       123456          COOL938 ...     ...             ... 

Thank you for your help and guidance.

Answer 1

UPDATE TABLE2        JOIN TABLE1        ON TABLE2.SERIAL_ID = TABLE1.SUBST_ID SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID; 

Answer 2

In addition to Tom's answer if you need to repeat the operation frequently and want to save time you can do:

UPDATE TABLE1        JOIN TABLE2        ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID WHERE TABLE2.BRANCH_ID IS NULL