CODEWITHSUNDEEP
mysqlsql
madiluzi
madiluzi

Reputation: 129

#1093 - Table is specified twice, both as a target for 'UPDATE' and as a separate source for data

I tried to update a field with multiplication and it shows error #1093 - Table is specified twice, both as a target for 'UPDATE' and as a separate source for data

This is my query

UPDATE `order_details` SET `price` = 
(SELECT (od.quantity * p.pricePerUnit) 
FROM order_details od join products p ON od.id_product = p.id_product 
WHERE dp.id_product = p.id_product)

Is there any solution?

Upvotes: 1

Views: 1506

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can't quite do that in MySQL. The best approach is a JOIN:

UPDATE order_details od JOIN
       products p
       ON od.id_product = p.id_product
    SET price = od.quantity * p.pricePerUnit;

Upvotes: 1

Related Questions