Mysql Update select statement

Question

I am trying to run an update on the following select statement but i dont think mysql is very fond of my syntax, Please advise?

    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`

score 0 · Answer 1 · answered Mar 17 '15 at 06:19

Buddy, Try to refer the table names from which those columns come from in where condition and next time you need to make use of table_name aliases while using such big queries :)

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

score 0 · Answer 2 · edited May 23 '17 at 11:56

Try this, I can not test it as I don't have your database schema:

UPDATE `invoice_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
SET 
`invoice_lines`.`cost_price` = (quote_lines.`cost_price` * products.commision_pers/100),
`invoice_lines`.`list_price` = (quote_lines.`list_price` * products.commision_pers/100),
`invoice_lines`.`unit_price` = (quote_lines.`unit_price` * products.commision_pers/100)

Hope it gets you on the right track. Also check this related question

Mysql Update select statement

2 Answers2