WordPress.org

Ready to get started?Download WordPress

Forums

SQL queries to delete all posts with given tag. (1 post)

  1. Arfa__
    Member
    Posted 2 years ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.