Fails to DELETE a statement with GROUP BY and HAVING clauses

Question

I'm trying to delete some records through the following SQL statement but i'm getting the error #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 'GROUP BY tsa_.task_attachment_id H' at line 12:

Does anyone knows how can i solve the error?

DELETE
    ta
FROM `tasks_recursive` AS tr
INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
INNER JOIN (
    `tasks_attachments` AS tsa_
    INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
) ON tsa_.task_attachment_id = tsa.task_attachment_id
WHERE
    ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
GROUP BY
    tsa_.task_attachment_id
HAVING
    COUNT( DISTINCT( tr_.task_id ) ) = 1

I also tried the following sentence where i got this error #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 'GROUP BY tsa_.task_attachment_id HAVING COUNT( DISTINCT( tr_.task_id ' at line 12:

DELETE
    ta
FROM `tasks_recursive` AS tr
INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
INNER JOIN (
    `tasks_attachments` AS tsa_
    INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
) ON tsa_.task_attachment_id = tsa.task_attachment_id
WHERE
    ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
GROUP BY
    tsa_.task_attachment_id
HAVING
    COUNT( DISTINCT( tr_.task_id ) ) = 1

The above sentence looks for task_attachments rows which aren't used by nobody ecxept itself in the many_to_many tasks_attachments table which links the slave task_attachments table to the master tasks_recursive one.

In order to workaround the above issue i tried to wrap the above query but i'm getting this error #1093 - You can't specify target table 'ta_' for update in FROM clause:

DELETE ta_
                FROM `task_attachments` AS ta_
                WHERE ta_.id IN
(
                    SELECT
                        ta.id
                    FROM `tasks_recursive` AS tr
                    INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
                    INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
                    INNER JOIN (
                        `tasks_attachments` AS tsa_
                        INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
                    ) ON tsa_.task_attachment_id = tsa.task_attachment_id
                    WHERE
                        ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
                    GROUP BY
                        tsa_.task_attachment_id
                    HAVING
                        COUNT( DISTINCT( tr_.task_id ) ) = 1
                )

Lastly tried double wrap which also didn't work, this is the error #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 sub )' at line 25:

DELETE
FROM `task_attachments`
WHERE `task_attachments`.id IN
(
SELECT
ta_.id
FROM `task_attachments` AS ta_
WHERE ta_.id IN 
(
    SELECT
        ta.id AS taId
    FROM `tasks_recursive` AS tr
    INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
    INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
    INNER JOIN (
        `tasks_attachments` AS tsa_
        INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
    ) ON tsa_.task_attachment_id = tsa.task_attachment_id
    WHERE
        ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
    GROUP BY
        tsa_.task_attachment_id
    HAVING
        COUNT( DISTINCT( tr_.task_id ) ) = 1
    ) AS sub
)

Add another subquery this is a know mysql limitation `DELETE FROM(SELECT column FROM( yourquery)somealias)` — Mihai, Mar 07 '14 at 17:57
Hi, thanks both for the answer. Before posting i googled a bit so i already tried the suggested workarounds which didn't work. I posted the SQL queries and the errors, just in case. Thx. — peris, Mar 07 '14 at 18:09

score 2 · Accepted Answer · answered Mar 07 '14 at 18:25

Right, MySQL's DELETE supports a couple of clauses borrowed from SELECT syntax, such as JOIN and LIMIT, but not all the clauses. GROUP BY and HAVING are not supported.

As I read your query, it seems to be simpler than you're making it. You really want to delete task_attachments that belong to only one task_id. And you have some conditions on the file name and creator id.

You could use an exclusion join for that. That is, try to join to a different task that references the same attachment id, and if none is found, the OUTER JOIN will return nulls instead. Then you test for nulls in your WHERE clause, and when that happens, you've found an attachment referenced by only one task_id.

DELETE 
    ta
FROM task_attachments AS ta
INNER JOIN tasks_attachments AS tsa1 
    ON tsa1.task_attachment_id = ta.id
LEFT OUTER JOIN tasks_attachments AS tsa2 
    ON tsa1.task_attachment_id = tsa2.task_attachment_id
    AND tsa1.task_id <> tsa2.task_id 
WHERE
    ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
    AND ta.creator_id = 279
    AND tsa2.task_attachment_id IS NULL;

I didn't see that referencing task_recursive is necessary at all in this query, since the task_id is already present in tasks_attachments.

Before you delete anything, it would of course be better to try that as a SELECT first, to double-check that my reformulation gives you what you need.

SELECT 
    ta.*
FROM task_attachments AS ta
INNER JOIN tasks_attachments AS tsa1 
    ON tsa1.task_attachment_id = ta.id
LEFT OUTER JOIN tasks_attachments AS tsa2 
    ON tsa1.task_attachment_id = tsa2.task_attachment_id
    AND tsa1.task_id <> tsa2.task_id 
WHERE
    ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
    AND ta.creator_id = 279
    AND tsa2.task_attachment_id IS NULL;

Thanks a lot, you really wrote a simpler sentence while solving my problem. Thanks a lot man :D — peris, Mar 08 '14 at 08:38

score 0 · Answer 2 · answered Mar 07 '14 at 18:11

Try

DELETE
FROM `task_attachments`
WHERE `task_attachments`.id IN
(
SELECT
taId
FROM 
(
    SELECT
        ta.id AS taId
    FROM `tasks_recursive` AS tr
    INNER JOIN `tasks_attachments` AS tsa ON tsa.task_id = tr.id
    INNER JOIN `task_attachments` AS ta ON tsa.task_attachment_id = ta.id AND ta.creator_id = 279
    INNER JOIN (
        `tasks_attachments` AS tsa_
        INNER JOIN `tasks_recursive` AS tr_ ON tr_.id = tsa_.task_id
    ) ON tsa_.task_attachment_id = tsa.task_attachment_id
    WHERE
        ta.file_name IN ( '000531994879c3bf.pdf', '000531994879c5a8.pdf' )
    GROUP BY
        tsa_.task_attachment_id
    HAVING
        COUNT( DISTINCT( tr_.task_id ) ) = 1
    ) AS sub
)

Fails to DELETE a statement with GROUP BY and HAVING clauses

2 Answers2