MYSQL delete all rows, but the same query on select return only 3 rows

I can’t understand why the same query for select and delete have different behavior.
I need to delete all rows except 5 newest rows.
I know my solution for this task is no good, but my question is about why MySQL no delete the same rows, that return select for the same query clause

see code

drop table if exists tbl;
create table tbl
(
    id         serial,
    cal        date COMMENT 'some column',
    created_at datetime default NOW()
);

insert into tbl
values
       (default, '2018-07-15', '2018-07-15 12:00'),
       (default, '2018-07-16', '2018-07-16 12:00'),
       (default, '2018-07-17', '2018-07-17 12:00'),
       (default, '2018-07-18', '2018-07-18 12:00'),
       (default, '2018-08-01', '2018-08-01 12:00'),
       (default, '2018-08-04', '2018-08-04 12:00'),
       (default, '2018-08-16', '2018-08-16 12:00'),
       (default, '2018-08-17', '2018-08-17 12:00');

  select *
  from tbl;

    #     +----+------------+---------------------+
    #     | id | cal        | created_at          |
    #     +----+------------+---------------------+
    #     | 1  | 2018-07-15 | 2018-07-15 12:00:00 |
    #     | 2  | 2018-07-16 | 2018-07-16 12:00:00 |
    #     | 3  | 2018-07-17 | 2018-07-17 12:00:00 |
    #     | 4  | 2018-07-18 | 2018-07-18 12:00:00 |
    #     | 5  | 2018-08-01 | 2018-08-01 12:00:00 |
    #     | 6  | 2018-08-04 | 2018-08-04 12:00:00 |
    #     | 7  | 2018-08-16 | 2018-08-16 12:00:00 |
    #     | 8  | 2018-08-17 | 2018-08-17 12:00:00 |
    #     +----+------------+---------------------+

now I need delete rows with id 1,2,3

SET @row_number = 0;
select *
from tbl
where tbl.id in (
    select T.id
    from (SELECT (@row_number := @row_number + 1) as num, tbl.id
          from tbl
          order by created_at desc
         ) as T
    where T.num > 5);


# +----+------------+---------------------+
# | id | cal        | created_at          |
# +----+------------+---------------------+
# | 3  | 2018-07-17 | 2018-07-17 12:00:00 |
# | 2  | 2018-07-16 | 2018-07-16 12:00:00 |
# | 1  | 2018-07-15 | 2018-07-15 12:00:00 |
# +----+------------+---------------------+

Now I use delete operation

SET @row_number = 0;
delete
from tbl
where tbl.id in (
    select T.id
    from (SELECT (@row_number := @row_number + 1) as num, tbl.id
          from tbl
          order by created_at desc
         ) as T
    where T.num > 5);

select * from tbl; # <-- result empty
# +----+-----+------------+
# | id | cal | created_at |
# +----+-----+------------+

I cry;

Answer

We can try doing a delete limit join here:

DELETE t1
FROM tbl t1
LEFT JOIN
(
    SELECT id
    FROM tbl
    ORDER BY created_at DESC
    LIMIT 5
) t2
    ON t1.id = t2.id
WHERE
    t2.id IS NULL;

The idea behind this anti-join is that we will delete any record which does not match to one of the first five records, as ordered descending by the created_at column.

Note that we can’t use a WHERE IN query here, because MySQL will return the dreaded error message that LIMIT is not yet supported in this version.

Advertisement

Answer