2

I'm trying to delete all rows from a table except the one having the max revision_id value simultaneously selecting data from the same table:

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
    )

That throws the error

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

Is it possible to somehow change the query in order to achieve the goal?

JoSSte
  • 2,953
  • 6
  • 34
  • 54
stckvrw
  • 1,689
  • 18
  • 42

1 Answers1

3

This is a documented "feature" of MySql, you cannot update the same table as you select from, however a few workarounds exist, for example you can try nesting the query one level deeper:

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

See a working demo

Stu
  • 30,392
  • 6
  • 14
  • 33
  • Thanks! Is it a typo `)b` before the last row? – stckvrw Dec 11 '22 at 16:17
  • that is a shorthand `as b` – JoSSte Dec 11 '22 at 16:19
  • 1
    I haven't tried the code but no, `b` is the required derived table alias. – Stu Dec 11 '22 at 16:19
  • @Stu I don't know why, but it doesn't work. I get "0 rows affected" and when I run select, I still see all the rows in the table – stckvrw Dec 11 '22 at 16:49
  • @Stu Perhaps because `revision_id` is the primary key? – stckvrw Dec 11 '22 at 17:01
  • 1
    @stckvrw I spotted the possible problem I think, I edited above, the `max(revision_id)` should also be aliased otherwise it won't exist for the outer query - see [working Fiddle](https://dbfiddle.uk/WvsvVa0E) – Stu Dec 11 '22 at 17:09