You can't specify target table 'merchant_customer_manager' for update in FROM clause

这个错误通常是因为 MySQL 不允许在一个查询中同时更新和查询同一个表。解决方法是使用子查询将要更新的行先查询出来，然后再更新。例如：

UPDATE merchant_customer_manager
SET is_default = 0
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM merchant_customer_manager
    WHERE merchant_id = 123 AND is_default = 1
    ORDER BY created_at DESC
    LIMIT 1
  ) AS tmp
);

这里先使用子查询查询出要更新的行的 ID，然后在外部的 UPDATE 语句中使用 IN 子句更新这些行的 is_default 字段。这样可以避免在同一个查询中同时更新和查询同一个表的错误。

相关问题

You can t specify target table res_device_info for update in FROM clause

This error message usually occurs in SQL when you try to update a table that is also used in the FROM clause of the same query. To resolve this issue, you can use a subquery to update the table instead. Here's an example:

UPDATE res_device_info
SET column_name = new_value
WHERE device_id IN (
  SELECT device_id
  FROM res_device_info
  WHERE condition
);

In this example, you can replace `column_name`, `new_value`, `condition` with the appropriate values for your scenario. Make sure to adjust the subquery's condition to match your requirements.

You can't specify target table 'T_OB_CALLHIS_332000' for update in FROM clause

这个错误是因为在更新语句中，目标表和子查询中的同一个表不能同时出现，需要将子查询的结果先存储到一个临时表中再进行更新操作。你可以尝试使用以下语法来解决这个问题：

UPDATE T_OB_CALLHIS_332000 
SET STATUS = '0' 
WHERE ID IN (
  SELECT * FROM (
    SELECT ID 
    FROM T_OB_CALLHIS_332000 
    WHERE STATUS = '1' 
    AND CREATE_TIME < '2022-01-01 00:00:00' 
  ) AS TEMP
);

这样就可以避免出现 "You can't specify target table 'T_OB_CALLHIS_332000' for update in FROM clause" 的错误了。