Mysql更新子查询指定目标表

发布于 2024-10-27 21:46:52 字数 623 浏览 5 评论 0原文

我在通过选择表中已有的最高 Final_id 并添加 +1 来更新 Final_id 时遇到问题。

下面的查询输出错误：“您无法在 FROM 子句中指定用于更新的目标表‘customer_orders’”，遗憾的是我不明白为什么......

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}'

我正在尝试为系统中的最终订单设置唯一的递增 ID。

我希望有人能告诉我我做错了什么！

此致

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

把梦留给海 2024-11-03 21:46:52

您可以重写查询并使用 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}'

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}'

回复收藏 0 原文

浊酒尽余欢 2024-11-03 21:46:52

将内部查询更改为 SELECT max(final_id) FROM customer_orders

回复收藏 0 原文

绝影如岚 2024-11-03 21:46:52

试试这个：

 UPDATE customer_orders        
    SET final_id = MT.MaxId + 1           -- use the computed max id, and increment
      , status = 2
      , payment_id = '{$transaction_id}'
      , payment_type = '{$type}'
      , payment_reserved = '{$amount}'
      , payment_currency = '{$cur}'
      , payment_cardnopostfix = '{$postfix}'
      , payment_fraud_suspicious = '{$fraud}'
   FROM customer_orders
      -- include a subquery to determine the max id from the customer_orders table
      -- and assign 'MT' as the name of the results table
      , (SELECT MAX(final_id) as MaxId FROM customer_orders) MT
  WHERE id = '{$order_id}'

Try this:

 UPDATE customer_orders        
    SET final_id = MT.MaxId + 1           -- use the computed max id, and increment
      , status = 2
      , payment_id = '{$transaction_id}'
      , payment_type = '{$type}'
      , payment_reserved = '{$amount}'
      , payment_currency = '{$cur}'
      , payment_cardnopostfix = '{$postfix}'
      , payment_fraud_suspicious = '{$fraud}'
   FROM customer_orders
      -- include a subquery to determine the max id from the customer_orders table
      -- and assign 'MT' as the name of the results table
      , (SELECT MAX(final_id) as MaxId FROM customer_orders) MT
  WHERE id = '{$order_id}'

回复收藏 0 原文

~没有更多了~