Update a table that I am sub-selecting from

问题:

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?

Error Message: 1093 - You can't specify target table 'a_fees' for update in FROM clause

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);

回答1:

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 and other_fees for every row that has the same fee_id, and then checks if it's < 5000.

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;

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);

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.

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;