There is a query that successfully returns the result:

SELECT * FROM tbl_article_tags t1 WHERE tag_id = '16256' AND EXISTS(SELECT 1 FROM tbl_article_tags t2 WHERE t1.article_id=t2.article_id AND tag_id = '17000')

Replaced SELECT * with DELETE , now throws an 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 't1 WHERE tag_id = '16256' ' at line 1

Does anyone have any idea why?

  • Do many entries delete such a query? And the primary key in the table for some fields, is there a separate ID field? (this would be to re-paste delete with a request that returns ID entries to be deleted, for example) - Mike
  • @Mike figure may be about 100k records. primary key id field. - Jeque
  • Those. at one time it will delete 100k records? Well, then the idea to get a list and delete it on non-working. long will choose each of the 100k. then as cheops said. through join. right in the join ON condition, specify t1.article_id=t2.article_id AND tag_id = '17000' because there is certainly one entry with this article_id and tag_id = 17000 - Mike
  • As far as I know, in mysql the operator delete does not work with subqueries - user193361
  • @ user193361 How it works, for example, how here ru.stackoverflow.com/questions/543375/… - Mike

2 answers 2

 DELETE A.* FROM tbl_article_tags A JOIN ( SELECT id FROM tbl_article_tags t1 WHERE tag_id = '16256' AND EXISTS(SELECT 1 FROM tbl_article_tags t2 WHERE t1.article_id=t2.article_id AND tag_id = '17000') ) B ON A.ID=B.ID

Or a bit easier:

 DELETE A.* FROM tbl_article_tags A JOIN tbl_article_tags B ON B.article_id=A.article_id AND B.tag_id = '17000' WHERE A.tag_id = '16256'

Which option will be faster in your case is difficult to say, check

    Working version

     DELETE FROM tbl_article_tags WHERE tag_id = '16256' AND EXISTS(SELECT 1 FROM tbl_article_tags t2 WHERE tbl_article_tags.article_id=t2.article_id AND tag_id = '17000')
    • throws error #1093 - You can't specify target table 'tbl_article_tags' for update in FROM clause - Jeque
    • ru.piliapp.com/mysql-syntax-check when checking the request, it shows that it was compiled correctly, well, now let's figure it out. - Legionary
    • In MySQL, this option does not work, you can not refer to a table in a subquery from which data is deleted. - cheops
    • @cheops hmm, weird .. stumbled upon searching for requests of this type. Here, for example, I found one on one forum: DELETE T FROM Table3 T WHERE EXISTS(SELECT * FROM Table3 TT WHERE TT.f1=T.f1 AND TT.ID<T.ID); wrote that it worked - Jeque
    • 2
      @EvgeniiZaets is a MySQL chip, here the removal is cursor, so this is a limitation. Through the self-join of the table if you do it, through the multi-table DELETE it will work. - cheops