Reputation: 13120
I want to update the total
column in table: tbl_orders
, by multiplying the quantity
column by the cost
column.
UPDATE `tbl_orders` SET
total = (SELECT quantity * cost AS n1 FROM `tbl_orders` WHERE orderid = 167 LIMIT 1)
WHERE orderid= 167 LIMIT 1
I've done subquery updates before, but the mysql error returned is:
You can't specify target table 'tbl_orders' for update in FROM clause
Can anyone see what I'm doing wrong?
Upvotes: 0
Views: 131
Reputation: 263733
JOIN
it instead.
UPDATE tbl_orders a
INNER JOIN
(
SELECT orderid, quantity * cost totalCost
FROM tbl_orders
WHERE orderid = 167
) b ON a.orderid = b.orderid
SET a.total = b.totalCost
WHERE a.orderid = 167
if you want to update all orderid
, remove all WHERE
clause.
Upvotes: 1