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
- Access your website’s cPanel or other hosting control panel and navigate to the phpMyAdmin tool. This is usually found under the “Databases” section.
- Select your WordPress database from the list of databases on the left-hand side of the screen.
- Click on the “Export” tab at the top of the screen.
- Under the “Export Method” section, select “Custom” instead of the default “Quick”.
- Choose the tables you want to backup. If you want to backup your entire database, ensure all tables are selected.
- 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.
- Under the “Compression” section, choose the compression type you want to use. If you’re unsure, select “None”.
- Click on the “Go” button at the bottom of the screen to start the backup process.
- 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
INNER JOIN wp_posts p2 ON p1.post_title = p2.post_title: This joins
wp_poststable again as
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”.