1

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
  • @nbk yes I want to keep the latest 500 records and delete the 10 oldest records. – Fakipo Jul 16 at 15:25
1

This query will update the oldest records that exceed the count of 500, so you keep 500 newest records for 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);
| improve this answer | |
  • 1
    May as well say LIMIT 500,1 (since you don't need multiple rows). Then you may as well switch from IN to EXISTS. – Rick James Jul 16 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.