I'm having trouble with updating final_id by selecting the highest final_id already in table and adding +1.
The query below outputs the error: "You can't specify target table 'customer_orders' for update in FROM clause" and I sadly fail to see why..
UPDATE customer_orders
SET final_id = (SELECT final_id FROM customer_orders ORDER BY final_id DESC)+1,
status = 2,
payment_id = '{$transaction_id}',
payment_type = '{$type}',
payment_reserved = '{$amount}',
payment_currency = '{$cur}',
payment_cardnopostfix = '{$postfix}',
payment_fraud_suspicious = '{$fraud}'
WHERE id = '{$order_id}'
I'm trying to set a unique increasing ID for finalized orders in my system.
I hope someone can tell me what I'm doing wrong!
Best regards
You can rewrite your query and use join
UPDATE customer_orders
INNER JOIN (SELECT IFNULL(MAX(final_id),0) as max_id FROM customer_orders)a ON(1=1)
SET final_id = a.max_id+1, status = 2, payment_id = '{$transaction_id}',
payment_type = '{$type}', payment_reserved = '{$amount}',
payment_currency = '{$cur}', payment_cardnopostfix = '{$postfix}',
payment_fraud_suspicious = '{$fraud}'
WHERE id = '{$order_id}'