Mysql如何使用count函式更新n行？

在下表中，如果同一 orgid 的總記錄超過 500，我想設置 delete = true，並且我想根據 createdate 進行設置，這樣如果記錄超過 500，則舊記錄將被刪除，並使該 orgid 的總記錄為 500。

這是我的桌子

Table A
+----+-------+------------------+--------+------------+
| id | orgid | transactionvalue | delete | createdate |  
+----+-------+------------------+--------+------------+
|  1 |     1 |              123 | false  | 05-16-2020 |  
|  2 |     1 |              412 | false  | 07-16-2020 |  
|  3 |     2 |              762 | false  | 07-16-2020 |  
+----+-------+------------------+--------+------------+

這是我正在嘗試的查詢

update A set delete = true where orgid = 1 and (select count(*) as records from (select * from A order by createdate) as pseudotable)) >500

此查詢將更新超過 500 條計數的最舊記錄，因此您保留 500 條最新記錄orgid = 1

update A 
set `delete` = true 
where orgid = 1 AND id IN (
SELECT id FROM  A where orgid = 1
order by createdate DESC
LIMIT 500,18446744073709551615);

引用自：https://dba.stackexchange.com/questions/271054