主页 / dba / 问题 / 271054
Accepted
Fakipo
Asked: 2020-07-17 03:57:26 +0800 CST

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

  • 772

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

1 个回答

  1. Best Answer

    此查询将更新超过 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);
    • 1

相关问题