0

I am trying to do the following query:

DELETE FROM main_history WHERE id NOT IN (
  SELECT max(id) FROM main_history GROUP BY instance_id, xpath, new_value
)

However, I get the following error:

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

Is there a way to do this directly in mysql in a single query? I'm currently creating a tmp table like so:

INSERT INTO main_history_new (
    SELECT id, instance_id, territory_id, xpath, field, old_value, new_value, max(date)
    FROM main_history
    GROUP BY instance_id, xpath, new_value
)
David542
  • 104,438
  • 178
  • 489
  • 842
  • Temporary table? MySQL's usually not bad, but sometimes it's downright ridiculous how limited it is. – tadman Jan 05 '17 at 00:19
  • have you tried something like: `DELETE FROM (SELECT id FROM main_history WHERE....) as tmp_table` – Dominick Navarro Jan 05 '17 at 00:20
  • @tadman it seems like this way worked: `DELETE FROM main_history WHERE id NOT IN (select id from (SELECT max(id) FROM main_history h GROUP BY instance_id, xpath, new_value) x)` – David542 Jan 05 '17 at 00:29

0 Answers0