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

jslywgbw 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(331)

在下表中，如果同一组织的总记录数超过500，我想将delete设置为true，并且我想根据createdate执行此操作，这样如果记录数超过500，则删除旧记录，并使该组织的总记录数达到500。
这是我的table

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

sql mysql stored-procedures sql-update

来源：https://stackoverflow.com/questions/62935904/mysql-how-to-update-n-rows-using-count-function

3条答案

按热度按时间

mxg2im7a1#

使用子查询和联接更新

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

赞(0）回复(0）举报 2021-07-26

xggvc2p62#

你可以用 row_number() 要查找每个组织的第500条记录，然后使用该信息：

update tablea a join
       (select a2.org_id, a2.created_date as cutoff_created_date
        from (select a2.*,
                     row_number() over (partition by a2.org_id order by create_date desc) as seqnum
              from tablea a2
             ) a2
        where a2.seqnum = 500
       ) a2
       on a.org_id = a2.org_id and and
          a.created_date < a2.cutoff_created_date
    set delete = true;

赞(0）回复(0）举报 2021-07-26

von4xj4u3#

我没有尝试所有其他的答案，他们可能是正确的，但这是我的工作

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

赞(0）回复(0）举报 2021-07-26

我来回答

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

3条答案

相关问题

热门标签

最新问答