Three Methods to Delete Duplicate Posts in WordPress (Plugin, SQL, PHP Script)

Three Methods to Delete Duplicate Posts in WordPress

In WordPress content management, duplicate posts can appear in the database due to imports, synchronization, or operational errors. These duplicates not only clutter your content but can also negatively impact SEO. This article explains three methods to delete duplicate WordPress posts: using a plugin, directly manipulating the database, and executing a PHP script.

Method 1: Using a Plugin (Recommended)

For most users, using a plugin is the safest and simplest approach. The recommended plugin is Delete Duplicate Posts.

1. In your WordPress admin, go to "Plugins" → "Add New" and search for "Delete Duplicate Posts".
2. Install and activate the plugin.
3. After activation, you can typically find its functionality under the "Tools" menu or the plugin's settings page. Follow the instructions to scan and delete duplicate posts.

Advantages: Simple to use, no code required, low risk.
Note: It is recommended to back up your database before proceeding.

Method 2: Direct Database Manipulation

This method requires running SQL queries directly on your WordPress database and is suitable for users familiar with database management. Always back up your database before executing any queries.

-- Step 1: Create a temporary table to store the minimum ID for each duplicate post title group
CREATE TABLE my_tmp AS SELECT MIN(ID) AS col1 FROM wp_posts GROUP BY post_title;

-- Step 2: Delete posts whose IDs are not in the temporary table (i.e., the duplicates)
DELETE FROM wp_posts WHERE ID NOT IN (SELECT col1 FROM my_tmp);

-- Step 3: Drop the temporary table
DROP TABLE my_tmp;

Important Notes:

• The code above assumes your posts table uses the default prefix wp_. If your site uses a custom prefix (e.g., cd_), replace wp_posts with the actual table name (e.g., cd_posts).
• This query groups and deduplicates based on the post_title field. If your duplicate posts do not have identical titles, this method may not identify them.
• It is strongly advised to first test the query in a staging environment or use a SELECT statement to verify which data will be deleted before executing the DELETE operation.

Method 3: Using a PHP Script

You can also create a PHP file to perform database operations equivalent to Method 2. Save the following code as delete_duplicates.php and place it in your WordPress root directory.

<?php
// Load the WordPress environment
require_once('./wp-load.php');

global $wpdb; // Get the WordPress database object

// Use the correct table name via $wpdb->posts
$table_name = $wpdb->posts;

// Step 1: Create temporary table
$sql1 = "CREATE TABLE my_tmp AS SELECT MIN(ID) AS col1 FROM {$table_name} GROUP BY post_title";
$wpdb->query($sql1);

// Step 2: Delete duplicate posts
$sql2 = "DELETE FROM {$table_name} WHERE ID NOT IN (SELECT col1 FROM my_tmp)";
$wpdb->query($sql2);

// Step 3: Drop the temporary table
$sql3 = "DROP TABLE my_tmp";
$wpdb->query($sql3);

echo 'Duplicate post deletion completed.';
?>

Usage Steps and Warnings:

1. Access this file via your browser (e.g., https://yoursite.com/delete_duplicates.php) to execute the script.
2. Immediately delete this script file from your server after execution to prevent security risks.
3. As always, back up your database before running the script.

Summary and Recommendations

For general users, Method 1 (Plugin) is the preferred choice. For developers or administrators who must use code, Method 3 (PHP Script) is superior to Method 2 (Direct SQL) because it utilizes WordPress's database object ($wpdb), automatically handles table prefixes, and offers better compatibility. The original Method 2 SQL and initial Method 3 code used hard-coded table names (like cd_posts), which is not universal across WordPress installations; the revised code above corrects this issue.

Regardless of the method chosen, database backup is an essential prerequisite to avoid data loss.