Understanding the WordPress wp_postmeta Table: Managing Product Variations, SKUs, and Bulk Updates

Introduction

The wp_postmeta table in WordPress is a critical component of WooCommerce, storing custom metadata for posts, products, and variations. When managing an eCommerce store, understanding how data is structured in wp_postmeta is essential for efficiently updating SKUs, barcodes, descriptions, and variation attributes.

In this guide, we’ll explore:

• What is stored in wp_postmeta?
• How WooCommerce variations leverage this table.
• Finding product details using SKUs.
• Updating product variations in bulk with SQL queries.

1. What Is Stored in the wp_postmeta Table?

The wp_postmeta table is a key-value store linked to the wp_posts table. Each row represents additional metadata about a post or product.

wp_postmeta Table Structure

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) UNSIGNED NOT NULL,
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Common Metadata Keys for WooCommerce

• _sku – The stock-keeping unit (SKU) for products and variations.
• _barcode – Stores product barcode values if a plugin supports it.
• _regular_price – The standard price for the product.
• _sale_price – The discounted price if the product is on sale.
• _stock_status – Availability of the product (instock, outofstock).
• _variation_description – Stores descriptions for variations.
• _length, _width, _height, _weight – Physical dimensions of the product.
• attribute_* – Stores variation attributes (e.g., color, size, material).

Each product variation has its own post_id in wp_posts and its own metadata in wp_postmeta.

2. How WooCommerce Variations Use wp_postmeta

Understanding Variations in WordPress

When you create a variable product in WooCommerce, WooCommerce generates multiple product_variation posts in wp_posts and stores each variation’s details in wp_postmeta.

🔹 Parent Product (Variable Product) – A post of type product with metadata defining attributes.
🔹 Variations (Product Variations) – Each variation is stored as a separate post of type product_variation and linked to the parent product.

Example: Variation Metadata Structure

3. Finding Product Variations by SKU

To find the post_id of a product variation using an SKU:

SELECT post_id FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value = 'SHIRT-RED-M';

🔹 This will return the post_id, which links to the variation.

To get all metadata for the variation:

SELECT meta_key, meta_value
FROM wp_postmeta
WHERE post_id = (SELECT post_id FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value = 'SHIRT-RED-M');

To find all variations of a parent product, use:

SELECT p.ID, pm.meta_key, pm.meta_value
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_parent = (SELECT ID FROM wp_posts WHERE post_title = 'Shirt' AND post_type = 'product');

4. Bulk Updating Product Variations

Updating product variations in bulk can save significant time. Here are some useful SQL queries:

Updating Prices in Bulk

To increase all product prices by 10%:

UPDATE wp_postmeta
SET meta_value = meta_value * 1.10
WHERE meta_key = '_regular_price';

To set all sale prices to 20% off:

UPDATE wp_postmeta
SET meta_value = meta_value * 0.80
WHERE meta_key = '_sale_price';

Updating Stock Status in Bulk

To mark all products as out of stock:

UPDATE wp_postmeta
SET meta_value = 'outofstock'
WHERE meta_key = '_stock_status';

To set stock status for a specific product variation:

UPDATE wp_postmeta
SET meta_value = 'instock'
WHERE meta_key = '_stock_status'
AND post_id = (SELECT post_id FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value = 'SHIRT-RED-M');

Updating Variation Descriptions in Bulk

UPDATE wp_postmeta
SET meta_value = 'New variation description here'
WHERE meta_key = '_variation_description';

5. Optimizing Performance for Bulk Updates

When performing bulk updates, follow these best practices:

✅ Use Transactions – Wrap queries inside a transaction to ensure data integrity.

START TRANSACTION;
UPDATE wp_postmeta SET meta_value = meta_value * 1.10 WHERE meta_key = '_regular_price';
COMMIT;

✅ Indexing – Ensure that meta_key and meta_value columns are properly indexed for faster searches.
✅ Batch Processing – If updating thousands of products, break queries into smaller chunks.

Conclusion

The wp_postmeta table is the backbone of WooCommerce product management. Understanding its structure allows you to:
✅ Quickly find product variations using SKUs.
✅ Retrieve and modify product attributes like price, stock, and descriptions.
✅ Perform bulk updates efficiently using SQL queries.

By mastering wp_postmeta, you can enhance WooCommerce performance, streamline inventory management, and automate large-scale updates with ease.

🚀 Looking to optimize your WooCommerce store? AKADATA can help automate and manage bulk product updates efficiently!