How to Delete WordPress Duplicate Posts without Plugins

Posted on

Before we even begin with this, it’s highly recommended to create a backup of your WordPress database.

There are plugins available to create WordPress backup but you can create a WordPress database backup without using plugins.

Create database backup

  1. Access your website’s cPanel or other hosting control panel and navigate to the phpMyAdmin tool. This is usually found under the “Databases” section.
  2. Select your WordPress database from the list of databases on the left-hand side of the screen.
  3. Click on the “Export” tab at the top of the screen.
  4. Under the “Export Method” section, select “Custom” instead of the default “Quick”.
  5. Choose the tables you want to backup. If you want to backup your entire database, ensure all tables are selected.
  6. Under the “Output” section, choose the format of your backup file. It’s recommended to select “SQL” format as it is compatible with most database management systems.
  7. Under the “Compression” section, choose the compression type you want to use. If you’re unsure, select “None”.
  8. Click on the “Go” button at the bottom of the screen to start the backup process.
  9. Save the backup file to your local computer by clicking on the “Download” button when prompted.

That’s it! You now have a backup of your WordPress database that you can use to restore your website in case anything goes wrong. It’s recommended to perform regular backups to ensure that you always have a recent version of your website’s data.

Find all duplicate posts

To find duplicate entries in the WordPress posts table, you can use the following MySQL query:

SELECT post_title, COUNT(*) as count 
FROM wp_posts
WHERE post_type = 'post'
GROUP BY post_title
HAVING count > 1;

This query will select all posts from the wp_posts table where the post type is post. It will group the posts by their title and count the number of posts with each title. The HAVING clause will filter the results to show only the titles that appear more than once.

Note: The table prefix in this query (wp_) assumes that your WordPress database uses the default prefix. If your database uses a different prefix, you will need to modify the table name in the query accordingly.

Delete Duplicate posts in WordPress using SQL query

If you want to delete duplicate posts from the wp_posts table in WordPress and keep only one version, here’s a MySQL query:

DELETE p1 FROM wp_posts p1 
INNER JOIN wp_posts p2 
ON p1.post_title = p2.post_title 
AND p1.ID > p2.ID 
AND p1.post_type = 'post' 
AND p2.post_type = 'post';

This query uses a self-join on the wp_posts table to identify all posts with the same title and delete all except the one with the lowest ID.

Here’s how it works:

  • DELETE p1 FROM wp_posts p1: This starts the delete statement and selects the first instance of the wp_posts table as p1.
  • INNER JOIN wp_posts p2 ON p1.post_title = p2.post_title: This joins p1 with the wp_posts table again as p2 on the post_title field.
  • AND p1.ID > p2.ID: This condition ensures that we only delete duplicate posts with a higher ID than the lowest ID of posts with the same title.
  • AND p1.post_type = 'post' AND p2.post_type = 'post': This condition ensures that we only delete duplicates of posts with the post type of “post”.

Written by

I am a software engineer with over 10 years of experience in blogging and web development. I have expertise in both front-end and back-end development, as well as database design, web security, and SEO.