MS SQL SERVER and ORACLE.
I have a table Acct table that has acct_id and parent_acct_id attribute. Parent_acct_id on children account points to acct_id on parent account. If no parent, then parent_acct_id = null. Family only has two levels, no grandparent/grandchildren.
Now I want to delete some accounts. It has a temp(acct_id, save) table to store the data that need to be deleted.
I want to avoid partial deletion so I need a query to detect if the temp table has the entire family or just a part of family.
I already have a query to populate 'yes' value from parent to children or from children to parent. I only need a query to mark children as 'yes' if parent doesn't exist or mark parent as 'yes' when missing children.
I only came up with two separate queries but I want to optimize to only use 1 query.
--parent in the temp table but children are not UPDATE MAIN SET save = 'yes' FROM temp_table MAIN WHERE save IS NULL AND EXISTS (SELECT 1 FROM acct WHERE acct.parent_acct_id= MAIN.acct_id AND NOT EXISTS (SELECT 1 FROM temp_table temp WHERE acct.acct_id = temp.acct_id)) --children are in the temp table but parent is not UPDATE MAIN SET SAVE = 'yes' FROM temp_table MAIN WHERE SAVE IS NULL AND EXISTS (SELECT 1 FROM acct WHERE MAIN.acct_ID = acct.acct_ID AND acct.parent_acct_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM temp_table temp WHERE acct.parent_acct_id = temp.acct_id))
Any thoughts appreciated!