Pfthb

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

I think this should work

UPDATE secondTable
JOIN firsTable ON secondTable.SERIAL_ID = firsTable.SUBST_ID
SET BRANCH_ID = CREATED_ID

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='';

Using INNER JOIN:

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

Another alternative solution like below: Here I am using WHERE clause instead of JOIN

UPDATE 
 TABLE1,
 TABLE2
WHERE
 TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET 
 TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

You can use this too:

update TABLE1 set BRANCH_ID = ( select BRANCH_ID from TABLE2 where TABLE1.SUBST_ID = TABLE2.SERIAL_ID)

but with my experience I can say that this way is so slow and not recommend it!