如何通过加入选择进行更新 [英] How to update from select with a Join
本文介绍了如何通过加入选择进行更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何更新子查询中也存在的表?我必须分两个阶段进行吗? (创建一个临时表-将选定的数据放入其中,然后更新最终表)
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)
我正在尝试使用每个CTN的网络标签更新invoiceLine表.
I am trying to update the invoiceLine table with the label of the network for each CTN.
最终结果将是:
-
invoiceLine
invoiceLine
ctn network
1234 network1
2345 network2
3456 network1
我有下表:
-
invoiceLine
invoiceLine
ctn network
1234 null
2345 null
3456 null
终端
terminal
ctn network
1234 1
2345 2
3456 1
网络
network
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
但是MySQL抛出
错误代码:1093.您无法在FROM子句中指定目标表'invoiceLine'进行更新
Error Code: 1093. You can't specify target table 'invoiceLine' for update in FROM clause
推荐答案
UPDATE invoiceLine
INNER JOIN terminal
ON invoiceLine.ctn = terminal.ctn
INNER JOIN network
ON terminal.network = network.id
SET invoiceLine.network = network.label
这篇关于如何通过加入选择进行更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文