How to update from select with a Join
31,261
Solution 1
UPDATE invoiceLine
INNER JOIN terminal
ON invoiceLine.ctn = terminal.ctn
INNER JOIN network
ON terminal.network = network.id
SET invoiceLine.network = network.label
Solution 2
UPDATE invoiceLine SET network = (
SELECT label FROM network WHERE id = (
SELECT network FROM terminal WHERE terminal.ctn = invoiceLine.ctn
)
)
Author by
Manse
Web Developer using - Symfony - PHP - MySQL - JavaScript - JQuery - CSS Always on the look out for new opportunities ... message me on twitter @manseuk
Updated on July 09, 2022Comments
-
Manse almost 2 years
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
-
Manse over 12 yearsWorks a treat ... thanks :-) - will accept your answer in 6 mins, when SO lets me !
-
mlg over 7 yearsAnd the WHERE and LIMIT keywords would go after the SET keyword.