基于另一个表值的MySQL更新表

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.

SUBST_ID和SERIAL_ID都匹配，它需要从第一个表获取created_id并将其插入第二个表。

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.

感谢您的帮助和指导。

6 个解决方案

#1

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

#2

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

除了Tom的回答，如果你需要经常重复操作并且想节省时间，你可以这样做:

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

#3

I think this should work

我认为这应该行得通

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

#4

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

#5

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

另一种解决方案如下:这里我使用WHERE子句而不是JOIN

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

#6

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!

但是根据我的经验，我可以这么说，这种方法太慢了，而且不推荐它!

#1

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

#2