MySQL - 表指定两次作为更新目标和单独的数据源

Question

当我尝试执行这两个查询时:

UPDATE bills
SET id_bill_tmp = (SELECT IFNULL(id_bill_tmp, 0)+1 AS id_bill_tmp FROM bills)
WHERE id_interess = 1;

UPDATE bills
SET id_bill_tmp = (SELECT max(id_bill_tmp)+1 FROM bills)
WHERE id_interess = 1;

我得到:

table 'bills' is specified twice both as a target for 'update' and as a separate source for data

如何重写 UPDATE 才能不报告此错误？

Answer 1

您无法通过在单个查询中从同一表获取数据来更新表。为此，至少您应该使用临时表，否则您应该使用 View

使用您选择的查询创建 View

CREATE VIEW `view_name` AS SELECT IFNULL(id_bill_tmp, 0)+1 AS id_bill_tmp FROM bills

然后使用 View 更新表

UPDATE bills
SET id_bill_tmp = (SELECT id_bill_tmp FROM view_name)
WHERE id_interess = 1;