How to update from select with a Join

Question

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

Possible duplicate of [How to use JOIN in UPDATE query?](http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query) — Félix Adriyel Gagnon-Grenier, Mar 17 '16 at 17:24

score 48 · Accepted Answer · answered Oct 27 '11 at 13:07

48

UPDATE invoiceLine
    INNER JOIN terminal
        ON invoiceLine.ctn = terminal.ctn
    INNER JOIN network
        ON terminal.network = network.id
    SET invoiceLine.network = network.label

answered Oct 27 '11 at 13:07

Joe Stefanelli

132,803
19
237
235

Works a treat ... thanks :-) - will accept your answer in 6 mins, when SO lets me ! – Manse Oct 27 '11 at 13:09
And the WHERE and LIMIT keywords would go after the SET keyword. – mlg Jan 01 '17 at 03:14

score 5 · Answer 2 · answered Oct 27 '11 at 13:14

5

UPDATE invoiceLine SET network = (
    SELECT label FROM network WHERE id = (
        SELECT network FROM terminal WHERE terminal.ctn = invoiceLine.ctn
    )
)

answered Oct 27 '11 at 13:14

Salman A

262,204
82
430
521

How to update from select with a Join

2 Answers2

Linked

Related