Delete from the same table as a subquery

[Dani]

I want to run a query, of course more involved, but something roughly similar to:

DELETE from table
WHERE
    id IN (
        SELECT id
        FROM table
        WHERE ...
    )

Doing that gives me an error message that I can't delete from the same table as I'm selecting from in a subquery clause. Soooo, right now the process I use is to manually copy the table, run the query, and then delete the copy. What is a better way of doing this? Temporary tables?

[Reverend Jim]

Wouldn't you accomplish the same thing by

DELETE FROM table WHERE ...

Perhaps I could be of more help knowing more about the table.

[Dani]

OK, I think I got it figured out :)

CREATE TEMPORARY TABLE IF NOT EXISTS `table.tmp` AS (SELECT * FROM `table`);
DELETE from `table`
    WHERE
        id IN (
            SELECT id
            FROM `table.tmp`
            WHERE ...
        );

The temporary table is automatically deleted when the PHP script finishes executing.

[Dani]

Perhaps I could be of more help knowing more about the table.

An example of what I'm trying to do is remove the 'javascript' tag from all forum topics already tagged 'jquery', or remove the 'smartphone' tag from all forum topics already tagged 'iphone' or 'android'. Given a table called tags with columns name and article_id, here is the actual SQL query I was playing around with:

CREATE TEMPORARY TABLE IF NOT EXISTS `tags.tmp` AS (SELECT * FROM `tags`);
DELETE from `tags`
WHERE 
  name = 'smartphone' and
  article_id in (
      SELECT article_id
      FROM `tags.tmp`
      WHERE name IN ('android', 'iphone')
  );

It's illegal in MySQL to do the following, because you can't delete rows from the sample table you are selecting from within the same query, hence the reason for the temporary table:

DELETE from `tags`
WHERE 
  name = 'smartphone' and
  article_id in (
      SELECT article_id
      FROM `tags`
      WHERE name IN ('android', 'iphone')
  );

[Reverend Jim]

If this is a one-shot thing then I'll stop thinking about it, but it seems there must be a simpler way.

[Dani]

A little bit more than a "one-shot thing" in the sense that a variation has been added to a cron job to do regular tag clean-up/maintenance, but I don't believe there's any simpler way, although I'm open to suggestions.

[Reverend Jim]

what's the DDL for tags?

[Dani]

Do you mean the schema for the tags table?

name | article_id | timestamp

[Reverend Jim]

OK. After further thought I don't see another way of doing what you want.