Support » Plugins » SQL queries to delete all posts with given tag.

  • I need an sql query to delete all posts in a given category that have a given tag, that are older than a given date.

    I’ve already sorted out a query to delete content in a given category older than such a date:-

    DELETE a, b, c, r
    FROM wp_posts a
    LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
    LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
    LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
    LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
    LEFT JOIN wp_relevanssi r ON ( r.doc = a.ID )
    WHERE e.term_id IN (4,5)
    AND TO_DAYS( NOW( ) ) – TO_DAYS( post_date ) >= $postMaxAge

    This works fine, deletes all posts in categories 4 and 5 older than $postMaxAge days.

    I tried to extend it with a subquery to narrow results down to posts with a given tag, but ended up with SQL errors:-

    DELETE a, b, c, r
    FROM wp_posts a
    LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
    LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
    LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
    LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
    LEFT JOIN wp_relevanssi r ON ( r.doc = a.ID )
    WHERE e.term_id IN (4,5)
    AND TO_DAYS( NOW( ) ) – TO_DAYS( post_date ) >= $postMaxAge
    AND a.ID IN ( SELECT a.ID
    FROM wp_posts a
    LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
    LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
    LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
    LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
    WHERE e.term_id = 11 )

    I get this error:

    You can’t specify target table ‘a’ for update in FROM clause

    Any ideas? I’m less up on MySQL, MSSQL is more my thing… 🙂

    BTW the wp_relevanssi is a custom search plugin, index table. I want to clear foreign key rows out from here too.

  • The topic ‘SQL queries to delete all posts with given tag.’ is closed to new replies.