After a recent bulk product import for a WooCommerce installation we noticed an issue with the gallery images. The featured image for each product was set to the first image within the gallery for that product. This created a duplicate image issue where the image gallery on the product detail page would show the first image twice. The suggestion from the plugin’s documentation was to rerun the import specifically targeting those fields to fix the issue. This was less than ideal as we were dealing with a product list that was nearly 3,000 items long and took 20-25 minutes to fully import.

Because I knew the underlying data was fine, it was just being duplicated, I decided to use a MySQL query to update the database directly rather than running another import. Here is the query I ended up with:

UPDATE wp_postmeta a
JOIN wp_postmeta b
ON a.post_id = b.post_id
SET b.meta_value = REPLACE(b.meta_value, CONCAT(a.meta_value, ','), '')
WHERE a.meta_key = '_thumbnail_id' AND b.meta_key = '_product_image_gallery';


Because of the way that WordPress stores post meta I had to run a join on the wp_postmeta table against itself. Here is a break down of each line of the query:

UPDATE wp_postmeta a
JOIN wp_postmeta b

Here I am selecting the wp_postmeta table and joining it to itself in order to grab the two separate meta data values. The a table is for the _thumbnail_id field and the b table is for the _product_image_gallery field.

ON a.post_id = b.post_id

Now we use the post_id column to join together the rows by the post that they are associated with.

SET b.meta_value = REPLACE(b.meta_value, CONCAT(a.meta_value, ','), '')

After selecting and joining the tables I am setting the meta_value of the b table to be the value of the meta_value column with the duplicate image ID removed. It was made simpler because of the fact that I knew that the first value in that _product_image_gallery meta value was the duplicate. This meant I only had to search at the beginning and not worry about whether or not it was followed by a comma.

One important note: the reason I’m specifically targeting the b.meta_value column is because the b table is the table within the join that is targeting the _product_image_gallery meta field. If I were to use the a table it would assign the updated value to the _image_id meta field.

WHERE a.meta_key = '_thumbnail_id' AND b.meta_key = '_product_image_gallery';

Finally I am establishing which meta fields that I’m looking to target with the query.

By using this MySQL approach to fixing the meta data I was able to fix the meta fields with a single command that took roughly 3 seconds to process rather than running another full product import.