Mysql How to update n rows using count function?

Question

In the following table, I want to set delete = true if the total records for same orgid exceed 500 and I want to do it according to createdate such that if records exceed 500 the old records get deleted and make total records 500 for that orgid.

here is my 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 |  
+----+-------+------------------+--------+------------+

Here is the query I am trying

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

You could clarify and simplify by providing sample data for 3 companies (5 each) keeping 2. — P.Salmon
– P.Salmon, Commented Jul 16, 2020 at 13:36

Zaynul Abadin Tuhin · Accepted Answer · 2020-07-16 13:36:25Z

1

use subquery and join update

   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

answered Jul 16, 2020 at 13:36

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

P.Salmon Over a year ago

Createdate has to feature here somewhere surely?

Fakipo Over a year ago

order by createdate desc ?

Gordon Linoff · Accepted Answer · 2020-07-16 14:14:16Z

1

You can use row_number() to find the 500th record for each org and then use that information:

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;

answered Jul 16, 2020 at 14:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Fakipo · Accepted Answer · 2020-07-16 19:04:31Z

0

I did not try all of the other answers, they might be correct but this is what worked for me

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

answered Jul 16, 2020 at 19:04

Fakipo

3003 silver badges22 bronze badges

Collectives™ on Stack Overflow

Mysql How to update n rows using count function?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related