MySQL update table based on another tables value
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.
UPDATE TABLE2
JOIN TABLE1
ON TABLE2.SERIAL_ID = TABLE1.SUBST_ID
SET TABLE2.BRANCH_ID = TABLE1.CREATED_ID;
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
UPDATE TABLE2
JOIN TABLE1
ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET TABLE2.BRANCH_ID = TABLE1.CREATED_ID
WHERE TABLE2.BRANCH_ID IS NULL or TABLE2.BRANCH_ID='';