Check 2 tables, if match then update third table-CodePudding

I have 2 tables that have data in them, due to the size of the third table I don't want to run queries against it so I have created a secondary table with only product SKU and IDs.

Here is what I am trying to do but I am quite confused on where to start.

Optimistically there is a way to do this with a MySQL query and loop through all of the records.

if wp_stc_outofstock `product_number` == wp_stc_current_products `sku`
    
UPDATE wp_postmeta SET meta_value = 'outofstock' WHERE post_id = 'wp_stc_outofstock.product_number' AND meta_key = '_stock_status'

Any help is appreciated, I am sure I am not doing a great job explaining it.

CodePudding user response：

My best attempt given your limited explanation -

UPDATE `wp_stc_outofstock` `oos`
JOIN `wp_stc_current_products` `cp`
    ON `oos`.`product_number` = `cp`.`sku`
JOIN `wp_postmeta` `pm`
    ON `oos`.`product_number` = `pm`.`post_id`
    AND `pm`.`meta_key` = '_stock_status'
SET `pm`.`meta_value` = 'outofstock';

As @danblack suggested, perhaps you should be dealing with your performance concerns, particularly by looking at your indexing. As long as the tables are indexed appropriately, millions of rows is not an issue.