Manually delete post from database

Question

For whatever reason, I have a post and a page with the same slug name and it causes the db to hang. I can't alter the slug on either so I need to go manually delete the posts from the database and start over.

Now, I am willing to go pull up the post ID from wp_posts and wp_postmeta and manually delete each entry. Will this screw up anything else in the database assuming I have no comments on either post or page?

EDIT

I found this script and I think it's ok but I'm not sure if this takes care of revisions or anything else I would leave behind from manually deleting the post

DELETE a,b,c
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 )
WHERE a.ID = xxx;

Answer 1

Drop this into a file in your plugin directory and you should be able to do this from your WP installation using a query string.

/*
Plugin Name: Delete Specific Post
Description: Rid the post forever!
Version: 0.1
Author: WPSE
License: GPL2
*/

add_filter('query_vars', 'delete_post_query_var');
function delete_post_query_var($vars){
    $vars[] = 'delete_post';
    return $vars;
}

add_action('admin_init', 'manually_delete_post', 0);
function manually_delete_post(){

    if(!is_user_logged_in())
        return;

    if(!current_user_can('manage_options'))
        return;

    if(get_query_var('delete_post')){
        $id = esc_attr(get_query_var('delete_post'));
        $delete = wp_delete_post($id, true); //True force deletes the post and doesn't send it to the Trash
        if($delete)
            echo "Post $id deleted successfully!";
        else
            echo "Post $id was not deleted.";
        exit;
    }
}

All you need to do is make sure you're logged into an administrator account, then visit: http://yourdomain.com/wp-admin/?delete_post=POSTID

Answer 2

Ok, if you want to delete it completely, you have to delete post/page from wp_term_relationships table. But if the issue is only in the slug, you can update appropriate record in database and your slugs will be fixed. To update slug you need to run such query:

UPDATE wp_posts SET post_name = 'my-new-slug' WHERE ID = /*your post ID here*/

Answer 3

why not simply use wp_delete_post() ?

When the post and page goes, everything that is tied to it is deleted also. This includes comments, post meta fields, and relationships between the post and taxonomy terms.

Answer 4

I recently had this issue with a post-type I wanted to nuke, relationships everywhere and no DB enforced FK cascade.

/* DELETE REVISIONS */
DELETE posts
FROM
    `prefix_posts` AS posts
INNER JOIN `prefix_posts` AS parents ON posts.post_parent = parents.ID
WHERE
    parents.post_type = "myposttype"; /*REPLACE W/ parents.ID = "yourID"*/


/* DELETE POSTS */
DELETE
FROM
    `prefix_posts`
WHERE
    post_type = "myposttype"; /*REPLACE W/ ID = "yourID"*/


/* DELETE ORPHANED POST META */
DELETE
FROM
    `prefix_postmeta`
WHERE
`prefix_postmeta`.`post_id` NOT IN(
    SELECT
        `prefix_posts`.`ID`
    FROM
        `prefix_posts`
);

Are you using any plugins that create tables? (like post-2-post)

/* ORPHANED POST2POSTS */
DELETE
FROM
    `prefix_p2p`
WHERE
    `prefix_p2p`.`p2p_from` NOT IN(
        SELECT
            `prefix_posts`.`ID`
        FROM
            `prefix_posts`
);

DELETE
FROM
`prefix_p2p`
WHERE
    `prefix_p2p`.`p2p_to` NOT IN(
        SELECT
            `prefix_posts`.`ID`
        FROM
            `prefix_posts`
    );

its probably easier to use the built in WP functions... but if you need SQL calls for this look above.