Mysql 更新 select 语句

Question

我正在尝试对以下 select 语句运行更新，但我认为 mysql 不太喜欢我的语法，请告知？

    UPDATE `invoice_lines`
SET 
`invoice_lines`.`cost_price` = Costnew,
`invoice_lines`.`list_price` = Listnew,
`invoice_lines`.`unit_price` = Unitnew
SELECT (quote_lines.`list_price` * products.commision_pers/100) AS Listnew, 
(quote_lines.`cost_price` * products.commision_pers/100) AS Costnew,
(quote_lines.`unit_price` * products.commision_pers/100) AS Unitnew
FROM `quote_lines`
INNER JOIN quotes
ON quotes.id = quote_lines.`quote_id`
INNER JOIN QuotePers
ON quoteid = quotes.id
INNER JOIN products
ON products.id = quote_lines.`related_id`
INNER JOIN invoice
ON invoice.`from_quote_id` = QuotePers.quoteid
INNER JOIN invoice_lines
ON invoice_lines.`invoice_id` = invoice.id
WHERE products.id = invoice_lines.`related_id`
AND  prodid = invoice_lines.related_id
AND invoice_id = invoice.id
GROUP BY  quotes.id,products.`id`

Answer 1

兄弟，尝试在 where 条件中引用这些列所在的表名称，下次在使用如此大的查询时需要使用 table_name 别名:)

UPDATE invoice_lines
SET 
invoice_lines.cost_price = Costnew,
invoice_lines.list_price = Listnew,
invoice_lines.unit_price = Unitnew
SELECT (quote_lines.list_price * products.commision_pers/100) AS Listnew, 
(quote_lines.cost_price * products.commision_pers/100) AS Costnew,
(quote_lines.unit_price * products.commision_pers/100) AS Unitnew
FROM quote_lines
INNER JOIN quotes
ON quotes.id = quote_lines.quote_id
INNER JOIN QuotePers
ON quote_lines.quoteid = quotes.id
INNER JOIN products
ON products.id = quote_lines.`related_id`
INNER JOIN invoice
ON invoice.from_quote_id = QuotePers.quoteid
INNER JOIN invoice_lines
ON invoice_lines.invoice_id = invoice.id
WHERE products.id = invoice_lines.related_id
AND quote_lines.prodid = invoice_lines.related_id
AND quote_lines.invoice_id = invoice.id
GROUP BY  quotes.id,products.id