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
x
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;
Advertisement
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.