mysql DELETE and limit

Question

I have a query that is working to display rows who contain specific meta key.

select wp_woocommerce_order_itemmeta.*
from wp_posts, wp_postmeta
inner join wp_woocommerce_order_items, wp_woocommerce_order_itemmeta
where wp_posts.post_type = "shop_subscription"
and wp_postmeta.post_id = wp_posts.ID
and wp_postmeta.meta_key = "_shipping_country"
and wp_postmeta.meta_value = "FR"
and wp_woocommerce_order_items.order_id = wp_posts.ID
and wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
and wp_woocommerce_order_itemmeta.meta_key = "_subtracted_base_location_taxes"

I want to delete these rows but with a limit. I ve got an error with this sql

DELETE wp_woocommerce_order_itemmeta.*
FROM wp_posts, wp_postmeta
inner join wp_woocommerce_order_items, wp_woocommerce_order_itemmeta
WHERE wp_posts.post_type = "shop_subscription"
and wp_postmeta.post_id = wp_posts.ID
and wp_postmeta.meta_key = "_shipping_country"
and wp_postmeta.meta_value = "FR"
and wp_woocommerce_order_items.order_id = wp_posts.ID
and wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
and wp_woocommerce_order_itemmeta.meta_key = "_subtracted_base_location_taxes"
LIMIT 100

I have read that you can't use LIMIT directly within DELETE when you're referencing multiple tables at the same time. I begin with mysql and i am stuck. Any help would be apreciate.

Thanks

Try DELETE wp_woocommerce_order_itemmeta , removing dot and asterisk — Fran Cerezo, Dec 14 '21 at 11:24
@FranCerezo i tried your solution by removing dot and asterisk but same error from mysql #1064 — RG2022, Dec 14 '21 at 11:43
@ProGu Thanks for your solution but as i said, i am too rookie to build this sort of query unfortunatly — RG2022, Dec 14 '21 at 11:43
@RG2022 : wp_woocommerce_order_itemmeta has any primary key ? and what is the primary key columns name ? — Ali Fidanli, Dec 14 '21 at 12:21
@AliFidanli wp_woocommerce_order_itemmeta (primary key is meta_id) wp_woocommerce_order_items (primary key is order_item_id) wp_posts (primary key is ID) wp_postmeta (primary key is meta_id) — RG2022, Dec 14 '21 at 13:13
@RG2022 posted an answer - it should work - couldnt test it since i dont have any fiddle to work on it — Ali Fidanli, Dec 14 '21 at 14:15

score 0 · Answer 1 · answered Dec 14 '21 at 14:47

try something like this

delete a
from wp_woocommerce_order_itemmeta a
join (
  select wp_woocommerce_order_itemmeta.meta_id
  from wp_posts, wp_postmeta
  inner join wp_woocommerce_order_items, wp_woocommerce_order_itemmeta
  where wp_posts.post_type = 'shop_subscription'
  and wp_postmeta.post_id = wp_posts.ID
  and wp_postmeta.meta_key = '_shipping_country'
  and wp_postmeta.meta_value = 'FR'
  and wp_woocommerce_order_items.order_id = wp_posts.ID
  and wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
  and wp_woocommerce_order_itemmeta.meta_key = '_subtracted_base_location_taxes'
  limit 100
) b on a.meta_id = b.meta_id

Thank you Sir! Work perfectly Thanks for your time – RG2022 Dec 14 '21 at 15:04 — RG2022, Dec 14 '21 at 15:04

Tiu Cristi · Answer 2 · 2021-12-14T11:50:33.470

-1

mysql delete with inner joins and limit

edited Dec 14 '21 at 11:50

answered Dec 14 '21 at 11:28

Tiu Cristi

1
1

mysql DELETE and limit

2 Answers2