CODEWITHSUNDEEP
mysqlsqlsubquerysql-delete
usumoio
usumoio

Reputation: 3568

Delete with subquery that references original table

I am working on the following query that deletes from my tags_users table based on the a sub-query that references that same table. Additionally, a field from the parent table is referenced. If a user has a certain type of tag i.e. job then their test-alert tag has to go away. I do not think this will work as a join because the tags_users table is a standard HABTM relation table so each row references one relation and there is no way to know what other tags a user might have based on a join. This is the query:

    DELETE tag_user_alias 
    FROM tags_users tag_user_alias
    WHERE 
        tag_user_alias.tag_id = 1118 
        AND EXISTS ( 
            SELECT 
                inner_tags_users.user_id 
            FROM 
                tags_users AS inner_tags_users, tags 
            WHERE 
                inner_tags_users.tag_id = tags.id AND
                inner_tags_users.user_id = tag_user_alias.user_id AND 
                tags.tag_slug <> 'test-alert' AND
                tags.tag_slug LIKE 'job%'
        );

I am trying to make this query work via an alias but I keep hitting the following error:

#1093 - You can't specify target table 'tag_user_alias' for update in FROM clause

I'm not sure how to fix this such that the query successfully operates this delete. Does anyone know how to do this type of delete, i.e. reference the parent table in the sub-query and also use the same table for both?

Upvotes: 2

Views: 352

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65324

First of all: You can't use a table in a subquery, that is being written (i.e. inserted into, updated or deleted from) in the superquery.

That said, you should try a DELETE FROM JOIN:

DELETE
  tags_users_ta.* 
FROM 
  tags_users AS tags_users_ta 
  INNER JOIN tags AS tags_ta ON tags_users_ta.tag_id=tags_ta.id
  INNER JOIN tags_users AS tags_users_job ON tags_users_ta.user_id=tags_users_jon.user_id
  INNER JOIN tags AS tags_job ON tags_users_job.tag_id=tags_job.id
WHERE
  tags_ta.tag_slug = 'test-alert' AND
  tags_job.tag_slug LIKE 'job%'

should do the trick, if I understand your schema correctly

Upvotes: 3

Related Questions