#title Database Orphans
#author Stefan Hornburg (Racke)
#topics MySQL; PostgreSQL
#teaser Deleting from table where record is missing in other table
#lang en


** 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);
}}}

<example>

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);

</example>

MySQL example:

<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;

</example>

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';