mysql DELETE and limit-CodePudding

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

CodePudding user response：

You can create a query which returns ID's (in your case it should be meta_id because you want to delete from table : wp_woocommerce_order_itemmeta ) and use limit inside to get only 100 records : LIMIT 100

Afterwards, you can use these ID's to delete from the table like below :

DELETE FROM wp_woocommerce_order_itemmeta D WHERE D.meta_id IN 
(
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
)

CodePudding user response：

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

CodePudding user response：

mysql delete with inner joins and limit