更新我从中子选择的表 [英] Update a table that I am sub-selecting from

问题描述

当我尝试更新 a_fees 表时，为什么会出现以下错误?据我所知，您不能将子选择标准基于正在更新的表?是因为 SQL 是向后读取的吗?我该如何解决这个问题?

Why do I get the following error when I try to update the a_fees table? From what I can gather, you can not base the sub-selection criteria on a table that is being updated? Is it because SQL is read backwards? How can I get around this?

错误消息:1093 - 您无法在 FROM 子句中指定更新目标表a_fees"

UPDATE a_fees
SET active = 'N'
WHERE a_fees.fee_id IN
(
SELECT fee_id
FROM a_fees
WHERE verified = 'N'
HAVING audit_fees + audit_related_fees + tax_fees + other_fees < 5000);

推荐答案

HAVING 子句与 GROUP BY 子句结合使用，你必须在 HAVING 子句上使用像 SUM 这样的聚合函数，如下所示:

The HAVING clause is used in combination with the GROUP BY clause, and you have to use an aggregate function like SUM on HAVING clause, like this:

SELECT fee_id
FROM a_fees
WHERE verified = 'N'
GROUP BY fee_id
HAVING SUM(audit_fees + audit_related_fees + tax_fees + other_fees) < 5000;

this sums autid_fees, audit_related_fees, tax_fees 和 other_fees 对于具有相同fee_id的每一行，以及然后检查它是否 <5000.

this sums autid_fees, audit_related_fees, tax_fees and other_fees for every row that has the same fee_id, and then checks if it's < 5000.

但如果 fee_id 是唯一的，则意味着您必须求和的所有字段都在同一行上，因此无需使用 GROUP BY 子句，您的查询可以简化为:

But if fee_id is unique, it means that all the fields you have to sum are on the same row, so there's no need to use a GROUP BY clause and your query could be simplified as this:

SELECT fee_id
FROM a_fees
WHERE
  verified = 'N'
  AND (audit_fees + audit_related_fees + tax_fees + other_fees) < 5000;

您的 UPDATE 查询将变为:

your UPDATE query then becomes:

UPDATE a_fees
SET active = 'N'
WHERE a_fees.fee_id IN (
  SELECT fee_id
  FROM a_fees
  WHERE verified = 'N'
    AND audit_fees + audit_related_fees + tax_fees + other_fees < 5000);

你是对的:MySql 不允许你更新你在选择部分使用的同一个表，你应该只使用 JOINS 重写更新查询.

And you're right: MySql doesn't allow you to update the same table you're using in the select part, and you should rewrite the update query using just JOINS.

但如果fee_id是唯一的，则不需要使用子查询，最好只使用没有子查询的UPDATE:

But if fee_id is unique there's no need to use a subquery and it's better to use just a UPDATE without a subquery:

UPDATE a_fees
SET active = 'N'
WHERE verified = 'N'
  AND audit_fees + audit_related_fees + tax_fees + other_fees < 5000;

这篇关于更新我从中子选择的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！