MySQL procedure to update ALT attribute in your product Images on Woocommerce

Updating ALT property in our images is something that helps us improve the SEO of your website. However, it can be a tough task when there are thousands of images. Here, there’s a procedure that we can use to update all the images at once.

CREATE DEFINER=user@localhost PROCEDURE updateAlts()
BEGIN

DECLARE title text DEFAULT "";
DECLARE id bigint unsigned default 0;
declare var_final integer default 0;

DECLARE cursor1 CURSOR FOR (SELECT p2.post_title, pm2.meta_value
FROM wp_posts AS p2 
LEFT JOIN wp_postmeta AS pm2 ON pm2.post_id = p2.id AND pm2.meta_key = '_thumbnail_id' 
LEFT JOIN wp_postmeta AS am2 ON am2.post_id = pm2.meta_value AND am2.meta_key = '_wp_attachment_image_alt' 
WHERE p2.post_type = 'product' AND p2.post_status = 'publish');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_final = 1;

OPEN cursor1;

bucle: LOOP

    FETCH cursor1 INTO title, id;

    IF var_final = 1 THEN
      LEAVE bucle;
    END IF;

    UPDATE wp_postmeta SET meta_value = title WHERE meta_key = '_wp_attachment_image_alt' and post_id = id;

END LOOP bucle;
CLOSE cursor1;
END

What we are doing is to create a Cursor that reads every single product title and updates the meta_key _wp_attachment_image_alt. Once we execute the procedure, all ALT attributes will be set.