How to update weight for multiple products in WooCommerce store

In WordPress, Woocommerce you need a mySQL UPDATE statement where you would be able to set weight, height, length, width=12 (for example) to all the instock products that have weight =0 or just blank value. Blank value might also mean that the value record is missing from wp_postmeta table So such a value has to be inserted/created, not only updated.

This is needed when you have many items that have a record of weight omitted when those were originally inserted to database and it is extremely long list to go thru and update manually. This might also need to be done now to enable shipping modules that depend on shipping weight and dimensions.

This first part updates existing weight, height, length, width values to 12 for in-stock products where weight and others are 0 or blank. However the records exist in the tables already. Hint: originally the records with this data do not get inserted when the product created if those are not manually specified. Those can exist only if they were specified and later removed in Product Data => Shipping on a product screen.

-- Update existing weight value to 12 for in-stock products where weight is 0 or blank
UPDATE wp_postmeta AS target
JOIN (
SELECT p.ID
FROM wp_posts AS p
JOIN wp_postmeta AS pm ON p.ID = pm.post_id
WHERE p.post_type = 'product'
AND p.post_status = 'publish'
AND pm.meta_key = '_stock_status'
AND pm.meta_value = 'instock'
) AS source ON target.post_id = source.ID
SET
target.meta_value = '12'
WHERE
target.meta_key IN ('_weight', '_height', '_length', '_width')
AND (target.meta_value = '0' OR target.meta_value = '');

Now we need to add the records if those never existed in the first place. So we check wp_postmeta and if those values “_weight”, etc. are absent, we create them and assign to 12 (or the value of your choice)

-- Insert or update weight, height, length, and width with a value of 12 for missing or blank values
INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT p.ID, defaults.meta_key, '12'
FROM wp_posts AS p
CROSS JOIN (
    SELECT '_weight' AS meta_key
    UNION ALL SELECT '_height'
    UNION ALL SELECT '_length'
    UNION ALL SELECT '_width'
) AS defaults
LEFT JOIN wp_postmeta AS existing_pm ON p.ID = existing_pm.post_id AND existing_pm.meta_key = defaults.meta_key
WHERE p.post_type = 'product'
AND p.post_status = 'publish'
AND p.ID IN (
    SELECT post_id
    FROM wp_postmeta
    WHERE meta_key = '_stock_status'
    AND meta_value = 'instock'
)
AND (
    (existing_pm.meta_id IS NULL)
    OR (existing_pm.meta_value = '')
);