MySQL 如何使用加入从选择更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7916186/
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
How to update from select with a Join
提问by Manse
How can I update a table that is also present in a subquery? Do I have to do it in 2 stages? (create a temporary table - put the selected data in it and then update the final table)
如何更新也存在于子查询中的表？我必须分两个阶段完成吗？（创建一个临时表 - 将选定的数据放入其中，然后更新最终表）
I am trying to update the invoiceLine table with the label of the network for each CTN.
我正在尝试使用每个 CTN 的网络标签更新 invoiceLine 表。
The end result would be:
最终结果将是：
invoiceLine
ctn network 1234 network1 2345 network2 3456 network1
发票行
ctn network 1234 network1 2345 network2 3456 network1
I have the following tables:
我有以下表格：
invoiceLine
ctn network 1234 null 2345 null 3456 nullterminal
ctn network 1234 1 2345 2 3456 1network
id label 1 network1 2 network2
发票行
ctn network 1234 null 2345 null 3456 null终端
ctn network 1234 1 2345 2 3456 1网络
id label 1 network1 2 network2
I can run a select but I'm not sure how to update with a join:
我可以运行一个选择，但我不确定如何使用连接进行更新：
update invoiceLine
inner join terminal on terminal.ctn = invoiceLine.ctn
set invoiceLine.network =
(
select network.label
from invoiceLine
inner join terminal on terminal.ctn = invoiceLine.ctn
inner join network on network.id = terminal.network
)
where invoiceLine.ctn = terminal.ctn
but MySQL throws a
但是 MySQL 抛出一个
Error Code: 1093. You can't specify target table 'invoiceLine' for update in FROM clause
错误代码：1093。您不能在 FROM 子句中指定更新的目标表“invoiceLine”
回答by Joe Stefanelli
UPDATE invoiceLine
INNER JOIN terminal
ON invoiceLine.ctn = terminal.ctn
INNER JOIN network
ON terminal.network = network.id
SET invoiceLine.network = network.label
回答by Salman A
UPDATE invoiceLine SET network = (
SELECT label FROM network WHERE id = (
SELECT network FROM terminal WHERE terminal.ctn = invoiceLine.ctn
)
)
