Database Orphans

Finding Orphans

With LEFT JOIN:

select PI.* from product_images PI LEFT OUTER JOIN products P on (P.sku = PI.sku) where P.sku is NULL;

With NOT EXISTS:

select PI.* from product_images PI WHERE NOT EXISTS (select * from products P where P.sku = PI.sku);

Deleting

With NOT EXISTS:

delete PI from product_images PI WHERE NOT EXISTS (select * from products P where P.sku = PI.sku);

delete from product_categories where category IN (select distinct category from product_categories PC LEFT JOIN categories C on PC.category = C.code WHERE code is NULL);

MySQL example:

delete PC from product_categories PC left join products P on PC.sku = P.sku where P.sku is NULL;

delete C from categories C left join product_categories PC on C.code = PC.category where C.parent > 0 and PC.category is NULL;

MySQL example for multiple table update:

update categories C left join product_categories PC on PC.category = C.code set count = count + 1 where PC.sku = 'ACN4551';