1

How can I accomplish what I'm trying to accomplish with the following query?

DELETE SomeDB.outertable 
FROM SomeDB.logging AS outertable
WHERE outertable.log_id NOT IN
    (
    SELECT SomeDB.f.log_id
    FROM
        (
        SELECT aa.*, MAX(aa.log_id) AS max_log_id
        FROM SomeDB.logging AS aa
        WHERE aa.operation_status = -1
        GROUP BY aa.process_id

        UNION ALL
        SELECT bb.*, MAX(bb.log_id) AS max_log_id
        FROM SomeDB.logging AS bb
        WHERE bb.operation_status = 1
        GROUP BY bb.process_id

        UNION ALL
        SELECT cc.*, MAX(cc.log_id) AS max_log_id
        FROM SomeDB.logging AS cc
        WHERE cc.operation_status = 0
        GROUP BY cc.process_id
        )
    AS x
    INNER JOIN SomeDB.logging AS f
        ON f.process_id = x.process_id
        AND f.log_id = x.max_log_id
    )

I've tried several variants of this, based on a bit of research around the web, but I always get the following error:

Error Code: 1093 You can't specify target table 'outertable' for update in FROM clause

Edit: I'm trying to clean up an event logging table by deleting all but the very last record of each type for each process.

tex
  • 2,756
  • 22
  • 31

1 Answers1

0

Remove the SomeDB.outertable from your query so it starts

DELETE FROM SomeDB.logging AS outertable
David Gillen
  • 1,172
  • 5
  • 14
  • Thanks, I've tried that one before. It results in Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS outertable WHERE outertable.log_id NOT IN ( SELECT SomeDB.f.log_id ' at line 2 – tex Feb 24 '11 at 16:34
  • Remove the `AS` clause, it is not part of the delete syntax. - http://dev.mysql.com/doc/refman/5.0/en/delete.html – David Gillen Feb 24 '11 at 16:45
  • This gets me back to Error 1093. Also, `AS` is a part of the `DELETE` syntax - check the docs more closely. It's covered under `table_references` in the `JOIN` syntax, which is referenced on the `DELETE` syntax page. See: http://dev.mysql.com/doc/refman/5.0/en/join.html – tex Feb 24 '11 at 16:51
  • You're not joining on the `DELETE` you're joining on the sub `SELECT`. What happens when you just run the sub query? – David Gillen Feb 24 '11 at 17:32
  • `AS` is allowed in a `DELETE` query, whether or not I'm joining on it as per the reference to `table_references` in the linked doc you provided. `AS` works in the original example I provided - no syntax error is thrown, the query executes for around one minute, then dies with the 1039 error, completely different than a syntax problem. The subquery alone works as expected - it returns exactly the records that I don't want to delete. – tex Feb 24 '11 at 18:03