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

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.

DELETE pm
FROM wp_postmeta pm
  LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL