SQL (deactivate vendors with 0 products)

deactivate.SQL

Replace wp_ with your actual table prefix.


-- 1) Preview vendors that will be affected
SELECT u.ID, u.user_login
FROM wp_users u
JOIN wp_usermeta cap 
  ON cap.user_id = u.ID
 AND cap.meta_key = 'wp_capabilities'
 AND cap.meta_value LIKE '%"vendor"%'
LEFT JOIN wp_posts p
  ON p.post_author = u.ID
 AND p.post_type = 'product'
 AND p.post_status NOT IN ('trash','auto-draft')
GROUP BY u.ID
HAVING COUNT(p.ID) = 0;

-- 2) Set existing vendor status rows to inactive
UPDATE wp_usermeta um
JOIN (
  SELECT u.ID
  FROM wp_users u
  JOIN wp_usermeta cap 
    ON cap.user_id = u.ID
   AND cap.meta_key = 'wp_capabilities'
   AND cap.meta_value LIKE '%"vendor"%'
  LEFT JOIN wp_posts p
    ON p.post_author = u.ID
   AND p.post_type = 'product'
   AND p.post_status NOT IN ('trash','auto-draft')
  GROUP BY u.ID
  HAVING COUNT(p.ID) = 0
) z ON z.ID = um.user_id
SET um.meta_value = 'inactive'
WHERE um.meta_key = '_wcv_vendor_status';

-- 3) Insert missing _wcv_vendor_status rows
INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
SELECT z.ID, '_wcv_vendor_status', 'inactive'
FROM (
  SELECT u.ID
  FROM wp_users u
  JOIN wp_usermeta cap 
    ON cap.user_id = u.ID
   AND cap.meta_key = 'wp_capabilities'
   AND cap.meta_value LIKE '%"vendor"%'
  LEFT JOIN wp_posts p
    ON p.post_author = u.ID
   AND p.post_type = 'product'
   AND p.post_status NOT IN ('trash','auto-draft')
  GROUP BY u.ID
  HAVING COUNT(p.ID) = 0
) z
LEFT JOIN wp_usermeta um
  ON um.user_id = z.ID
 AND um.meta_key = '_wcv_vendor_status'
WHERE um.umeta_id IS NULL;