coffeemonitor
coffeemonitor

Reputation: 13120

MySQL - Multiple 2 Columns, Update within Same Table

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

Answers (1)

John Woo
John Woo

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

Related Questions