UPDATE
`details_audit` a
SET
a.count_changes_below_100 = (
SELECT
COUNT(*)
FROM
`details_audit`
WHERE
sort_id < 100
);
This worked on a previous version of MySQL but not with version 8.0.43:
Error Code: 1093
You can't specify target table 'a' for update in FROM clause
How can I solve this?
In MySQL 8, you must put the subquery in a derived table:
UPDATE details_audit a
JOIN ( SELECT COUNT(*) AS cnt FROM details_audit WHERE sort_id < 100 ) b
SET a.count_change_below_100 = b.cnt
Based on the comment you have provided, this is what I could understand,
for each row, store the number of rows with sort_id < 100 and also sort_id < this row’s sort_id (i.e. how many changes happened before me, but only in the first 100 changes).
For that we could do something like this, rather than updating a column with the same value in every row.
UPDATE details_audit a
JOIN (
SELECT da1.id,
(
SELECT COUNT(*)
FROM details_audit da2
WHERE da2.sort_id < 100
AND da2.sort_id < da1.sort_id
) AS cnt
FROM details_audit da1
) x
ON a.id = x.id
SET a.count_changes_below_100 = x.cnt;
