How to delete rows from SQL table simultaneously selecting from the same table [duplicate]

Question

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?

does it work without the sub-select? as in delete from node_revision__body where entity_id=4 and revision_id not in (1,2,3) — JoSSte
– JoSSte, Commented Dec 11, 2022 at 16:04

Stu · Accepted Answer · 2022-12-11 17:08:40Z

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

edited Dec 11, 2022 at 17:08

answered Dec 11, 2022 at 16:12

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

stckvrw Over a year ago

Thanks! Is it a typo )b before the last row?

JoSSte Over a year ago

that is a shorthand as b

Stu Over a year ago

I haven't tried the code but no, b is the required derived table alias.

stckvrw Over a year ago

@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

Stu Over a year ago

@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

|

Collectives™ on Stack Overflow

How to delete rows from SQL table simultaneously selecting from the same table [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related