MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table node_revision__body(entity_id int, revision_id int);
insert into node_revision__body select 1,2;
insert into node_revision__body select 4,3;
insert into node_revision__body select 4,4;



Records: 1  Duplicates: 0  Warnings: 0



Records: 1  Duplicates: 0  Warnings: 0



Records: 1  Duplicates: 0  Warnings: 0


select * from node_revision__body;


entity_id
revision_id


1
2

4
3

4
4


delete from
  node_revision__body
where
  entity_id=4 
  and revision_id not in (
    select
      max(revision_id)
    from
      node_revision__body
    where
      entity_id=4
    );

You can't specify target table 'node_revision__body' for update in FROM clause


delete from node_revision__body
where entity_id = 4 
  and revision_id in (
    select revision_id from (
      select max(revision_id) revision_id
      from node_revision__body
      where entity_id = 4
    )b
);
select * from node_revision__body;


entity_id
revision_id


1
2

4
3