MySQL MySQL根据另一个表值更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12394506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL update table based on another tables value
提问by verheesj
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.
感谢您的帮助和指导。
回答by Tom
UPDATE TABLE2
JOIN TABLE1
ON TABLE2.SERIAL_ID = TABLE1.SUBST_ID
SET TABLE2.BRANCH_ID = TABLE1.CREATED_ID;
回答by RafaSashi
In addition to Tom's answer if you need to repeat the operation frequentlyand want to save timeyou 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
回答by Mohammad Imran
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='';
回答by Gonzalo.-
I think this should work
我认为这应该有效
UPDATE secondTable
JOIN firsTable ON secondTable.SERIAL_ID = firsTable.SUBST_ID
SET BRANCH_ID = CREATED_ID
回答by Faisal
Using INNER JOIN:
使用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 WHEREclause instead of JOIN
另一种替代解决方案如下：这里我使用WHERE子句而不是JOIN
UPDATE
TABLE1,
TABLE2
WHERE
TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET
TABLE2.BRANCH_ID = TABLE1.CREATED_ID;
回答by Abadis
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!
但根据我的经验，我可以说这种方式太慢了，不推荐！
