Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
587 views
in Technique[技术] by (71.8m points)

mysql - 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)

I am trying to update the invoiceLine table with the label of the network for each CTN.

The end result would be:

  • invoiceLine

    ctn       network
    1234      network1
    2345      network2
    3456      network1
    

I have the following tables:

  • 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

but MySQL throws a

Error Code: 1093. You can't specify target table 'invoiceLine' for update in FROM clause

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
UPDATE invoiceLine
    INNER JOIN terminal
        ON invoiceLine.ctn = terminal.ctn
    INNER JOIN network
        ON terminal.network = network.id
    SET invoiceLine.network = network.label

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...