1. Get the order ids for all orders that have a shipping state of "New Jersey".
SELECT orders.ID
FROM `wp_posts` orders
INNER JOIN `wp_postmeta` meta ON meta.post_id = orders.ID
WHERE
orders.post_type = 'shop_order' AND
meta.meta_key = '_shipping_state' AND
meta.meta_value = 'NJ';
Pedantic note: This query will capture only orders that have an explicit "Shipping" address. If no shipping address is provided, the billing address will need to be used:
SELECT orders.ID
FROM `wp_posts` orders
INNER JOIN (
SELECT
orders.ID,
(SELECT meta_value FROM wp_postmeta WHERE meta_key = '_billing_state' AND post_id = orders.ID) AS billing_state,
(SELECT meta_value FROM wp_postmeta WHERE meta_key = '_shipping_state' AND post_id = orders.ID) AS shipping_state
FROM `wp_posts` orders
WHERE orders.post_type = 'shop_order'
) states ON states.ID = orders.ID
WHERE
orders.post_type = 'shop_order' AND
( states.shipping_state = 'NJ' OR
( states.shipping_state IS NULL AND states.billing_state = 'NJ' ) )
2. Get the order ids for all orders that have shipping state of "New Jersey" and were created in the year 2021 (gmt).
SELECT orders.ID
FROM `wp_posts` orders
INNER JOIN `wp_postmeta` meta ON meta.post_id = orders.ID
WHERE
orders.post_type = 'shop_order' AND
meta.meta_key = '_shipping_state' AND
meta.meta_value = 'NJ' AND
orders.post_date_gmt BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59';
Pedantic note: Same as above:
SELECT orders.ID
FROM `wp_posts` orders
INNER JOIN (
SELECT
orders.ID,
(SELECT meta_value FROM wp_postmeta WHERE meta_key = '_billing_state' AND post_id = orders.ID) AS billing_state,
(SELECT meta_value FROM wp_postmeta WHERE meta_key = '_shipping_state' AND post_id = orders.ID) AS shipping_state
FROM `wp_posts` orders
WHERE orders.post_type = 'shop_order'
) states ON states.ID = orders.ID
WHERE
orders.post_type = 'shop_order' AND
orders.post_date_gmt BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59' AND
( states.shipping_state = 'NJ' OR
( states.shipping_state IS NULL AND states.billing_state = 'NJ' ) )
3. Get the order ids and billing totals for all orders created in 2021 that have a billing total higher or equal to $100.
SELECT
orders.ID,
meta.meta_value AS total
FROM `wp_posts` orders
INNER JOIN `wp_postmeta` meta ON meta.post_id = orders.ID
WHERE
orders.post_type = 'shop_order' AND
meta.meta_key = '_order_total' AND
meta.meta_value >= 100;
4. Update the tax status (_tax_status
) to "none" for products with an ID of 100, 101 or 102.
UPDATE `wp_postmeta`
SET `meta_value` = 'none'
WHERE
`meta_key` = '_tax_status' AND
`post_id` IN (100, 101, 102);