How to directly update WooCommerce product stock quantity by its sku

Question

A have a ton of variable products. I'm using cron to run the script every X minutes for fetching stock level from an external source. If I use wc_get_product_id_by_sku and update_post_meta it takes too long so I'm trying to find the way to use a custom prepared query. If I know product_id query would be like that:

$conn->prepare( "UPDATE wp_postmeta SET meta_value=? WHERE post_id=? AND meta_key=`_stock`" );

But I only know a product SKU. So how to modify this query to update _stock based on a product SKU?

post_id	meta_key	meta_value
10	_sku	ABCD
10	_stock	25

Answer 1

To squeeze out every last bit of performance I tuned the accepted answer even further. Instead of 3 queries per product we can use 2 queries by combining the ones for wp_postmeta. I personally use it to update 1.5 milion products.

public function update_product_stock($mysqli, $sku, $qty, $status)
{
    // Initialize post_id as null
    $post_id = null;

    // Prepare and execute query to find product ID by SKU
    $stmt = $mysqli->prepare("SELECT post_id FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value = ? LIMIT 1");
    if (! $stmt) {
        error_log("Prepare failed: " . $mysqli->error);
        return;
    }

    $stmt->bind_param("s", $sku);
    $stmt->execute();
    $stmt->bind_result($post_id);

    if ($stmt->fetch() && ! is_null($post_id)) {
        $stmt->close();

        // Update _stock and _stock_status in wp_postmeta
        $update_postmeta_query = "
            UPDATE wp_postmeta
            SET meta_value = CASE
                WHEN meta_key = '_stock' THEN ?
                WHEN meta_key = '_stock_status' THEN ?
            END
            WHERE post_id = ?
            AND meta_key IN ('_stock', '_stock_status')";

        $update_postmeta_stmt = $mysqli->prepare($update_postmeta_query);
        if (! $update_postmeta_stmt) {
            error_log("Prepare failed: " . $mysqli->error);
            return;
        }

        $update_postmeta_stmt->bind_param("ssi", $qty, $status, $post_id);
        $update_postmeta_stmt->execute();
        $update_postmeta_stmt->close();

        // Update stock_quantity in wc_product_meta_lookup
        $update_lookup_query = "
            UPDATE wp_wc_product_meta_lookup
            SET stock_quantity = ?, stock_status = ?
            WHERE product_id = ?";

        $update_lookup_stmt = $mysqli->prepare($update_lookup_query);
        if (! $update_lookup_stmt) {
            error_log("Prepare failed: " . $mysqli->error);
            return;
        }

        $update_lookup_stmt->bind_param("isi", $qty, $status, $post_id);
        $update_lookup_stmt->execute();
        $update_lookup_stmt->close();
    } else {
        $stmt->close();
        error_log("SKU not found: " . $sku);
    }
}

Answer 2

$wpdb and JOIN works too slow, so the fastest way is to use four separate prepared queries:

$conn = mysqli_init();
mysqli_real_connect( $conn, DB_HOST, DB_USER, DB_PASSWORD, DB_NAME );

// Get product ID by SKU
$stmt1 = $conn->prepare( "SELECT post_id FROM {$wpdb->prefix}postmeta WHERE meta_key = '_sku' AND meta_value = ?");

// Update stock level on postmeta table
$stmt2 = $conn->prepare( "UPDATE {$wpdb->prefix}postmeta SET meta_value = ? WHERE meta_key = '_stock' AND post_id = ?");

// Update stock data on wc_product_meta_lookup table
$stmt3 = $conn->prepare( "UPDATE {$wpdb->prefix}wc_product_meta_lookup SET stock_quantity = ?, stock_status = ? WHERE product_id = ?");

// Update stock status on postmeta table
$stmt4 = $conn->prepare( "UPDATE {$wpdb->prefix}postmeta SET meta_value = ? WHERE meta_key = '_stock_status' AND post_id = ?");

foreach ( $products as $product ) {
    $qty        = $product['ON_HAND'];
    $sku        = $product['PRODUCT_SKU'];
    $status     = $qty ? 'instock' : 'onbackorder';

    // Get product ID by SKU
    $stmt1->bind_param( "s", $sku );
    $stmt1->execute();

    $res = $stmt1->get_result();
            
    while ( $row = $res->fetch_assoc() ) {

        $id = $row['post_id'];

        // Update stock level on postmeta table
        $stmt2->bind_param( "dd", $qty, $id );
        $stmt2->execute();

        // Update stock data on wc_product_meta_lookup table
        $stmt3->bind_param( "dsd", $qty, $status, $id );
        $stmt3->execute();

        // Update stock status on postmeta table
        $stmt4->bind_param( "sd", $status, $id );
        $stmt4->execute();
   }
}

Answer 3

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?

Answer 4

You need first post id then you can update stock value. First get the post id by sku

global $wpdb;
$productId = $wpdb->get_var( $wpdb->prepare( "SELECT post_id FROM $wpdb->postmeta WHERE meta_key='_sku' AND meta_value='%s' LIMIT 1", $sku ) );

we will get product id then just guess $productValue = 50; then

if ( $product_id )
{
$wpdb->prepare( "UPDATE wp_postmeta SET meta_value=".$productValue." WHERE post_id=".$productId." AND meta_key=`_stock`" );
}

dear try this code it will work