WPML 3.9 introduced a possibility to display content coming from the original language when viewing pages in other languages. If your site relied on content duplication before the 3.9 version, you might want to delete all the duplicate content from your site before using this new translation mode.
How to use this tutorial
Steps provided on this page remove settings and contents from the database. Proceed only if you know what you are doing and absolutely start by creating a complete backup of your site’s database.
To use the provided SQL queries you must edit them:
- Replace XXXX with the post type you wish to delete duplicate contents for (e.g. “post”, “product”, or “page).
- Replace YYYY with the site’s default language code (e.g. “en”).
- Replace wp_ with the table prefix configured using the $table_prefix variable in the wp-config.php file (the default is wp_).
Finally, you need to run these queries using one of the tools for working with SQL databases, for example, phpMyAdmin.
1. Removing duplicated content
Delete duplicated content from wp_posts
DELETE FROM wp_posts
WHERE post_type = 'XXXX' AND ID IN (
SELECT pm.post_id
FROM wp_postmeta pm
JOIN wp_icl_translations t ON t.element_id = pm.meta_value
WHERE pm.meta_key = '_icl_lang_duplicate_of' AND pm.meta_value > 0 AND
t.element_type LIKE 'post_%' AND t.language_code = 'YYYY'
)
2. Removing translation settings
Delete translation settings from wp_icl_translations
DELETE FROM wp_icl_translations
WHERE element_type = 'post_XXXX' AND element_id IN (
SELECT pm.post_id
FROM wp_postmeta pm
JOIN wp_icl_translations t ON t.element_id = pm.meta_value
WHERE meta_key = '_icl_lang_duplicate_of' AND meta_value > 0 AND t.language_code = 'YYYY'
)
3. Removing orphaned post meta
Please note that the execution of this query is slow and may time out, depending on your server’s settings. Additionally, it is not mandatory to run this query as it is only used to clear your database of any orphaned post meta.
Deleting orphans from wp_postmeta
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL