WooCommerce How can I update the stock quantity of a product using the SKU as a reference in SQL language? [duplicate]

Solution 1:

You can use the following SQL query embedded in a function that will update product stock quantity based on the product SKU (the table wp_wc_product_meta_lookup needs also to be updated):

/*
 * Update stock quatity from a product sku
 *
 * @param int $stock_qty The new stock quatity
 * @param int $sku The product sku
 * @return int/null
 */
function update_stock_qty_from_product_sku( $stock_qty, $sku ) {
    global $wpdb;

    return $wpdb->query( $wpdb->prepare( "
        UPDATE {$wpdb->prefix}postmeta pm1
        INNER JOIN {$wpdb->prefix}postmeta pm2
            ON pm1.post_id =  pm2.post_id
        INNER JOIN {$wpdb->prefix}wc_product_meta_lookup pml
            ON pm1.post_id =  pml.product_id
        SET pm1.meta_value = %d, pml.stock_quantity = %d
        WHERE pm1.meta_key = '_stock'
            AND pm2.meta_key = '_sku'
            AND pm2.meta_value = '%s'
    ", $stock_qty, $stock_qty, $sku ) );
}

Code goes in functions.php file of the active child theme (or active theme). Tested and works.

Related: How can I do an UPDATE statement with JOIN in SQL Server?