«
»

I needed to clean out some orphaned records from a MySQL database today. I thought this would be as simple as

DELETE FROM subTable WHERE ID IN (
  SELECT subTable.ID from subTable
  LEFT OUTER JOIN parentTable ON subTable.parentID = parentTable.ID
  WHERE  parentTable.ID IS NULL
  )

But this resulted in an error:

ERROR 1093 (HY000): You can't specify target
table 'subTable' for update in FROM clause

In MySQL, you can’t delete (or update) a table when you are using that table in a subselect. Here is one work around (I’m sure there are many):

CREATE TEMPORARY TABLE deleteids AS (
  SELECT subTable.ID from subTable
  LEFT OUTER JOIN parentTable ON subTable.parentID = parentTable.ID
  WHERE  parentTable.ID IS NULL
);

DELETE FROM subTable WHERE ID IN (SELECT ID FROM deleteids);
DROP TABLE deleteids;

If you don’t explicitly drop your temporary table, it gets dropped automatically when you close your MySQL session.

This worked great for all the tables I needed clean up, except for one. This table has 150,000 records, with 10,000 of them being abandoned. The above solution created a query with an IN () statement containing 10,000 values!

Even when I broke it down to running 100 at a time, it was still running about 1 delete per second, and I’m sure it gets worse if you try to do more than 100 at a time. MySQL (And SQL Server as well) is not very efficient with IN() clauses. Thankfully, most (all?) subselects can be rewritten to use a join. In fact doing this way, I was able to eliminate the temp table.

DELETE subTable FROM subTable LEFT OUTER JOIN parentTable ON
subTable.parentID = parentTable.ID WHERE parentTable.ID IS NULL

This deleted all 10,000 records in two seconds.

This entry was posted on 11 April 2008 at 1:44 pm and is filed under SQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

5 Comments

  1. Arian says:

    ‘DELETE subTable FROM subTable’, is that a mistake?
    thought MYSQL syntax was ‘DELETE FROM subTable’?

    22 May 2008, 9:19 am

  2. Ryan Stille says:

    No, thats not a mistake. You are right that in a normal delete you would just say “DELETE FROM subTable”. But we are doing a join here. So I believe doing it this way indicates which table from all the tables listed you want to delete from. I’m not 100% sure if its required or not, maybe it will just delete from the first table listed if you didn’t explicitly say “DELETE subTable”.

    22 May 2008, 9:23 am

  3. Arian says:

    Just saying cause i was looking at the MYSQL5.1 manual

    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] …
    FROM table_references
    [WHERE where_condition]

    22 May 2008, 9:45 am

  4. Ryan Stille says:

    Yes – that documentation appears to support the syntax I used in this article, no?

    22 May 2008, 9:48 am

  5. Arian says:

    whoops more research found this…
    Thanks for the info! really useful
    ”
    For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

    DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
    “

    22 May 2008, 9:51 am