如何通过加入选择进行更新 [英] 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屋！