mysql - Mysql如何使用count函数更新n行？

Question

在下表中，如果同一 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

标签： mysqlsqlstored-proceduressql-update

Answer 1

使用子查询并加入更新

   UPDATE tablea
            INNER JOIN
        (select orgid, count(*) cnt from tablea group by orgid
        ) b ON tablea.orgid = b.orgid 
    SET 
        delete = 'true'
    where cnt>500