Detect if the family record is incomplete to avoid partial deletion

Question

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.

For example,

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!

Answer 1

Why further optimize two separate queries into one query? It only makes the query less readable, and more difficult to understand, or to make changes when this is needed (in the future):

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

Answer 2

Difficult to say without knowledge of the data held, schema details, hardware available, etc., but combining possibilities using a UNION ALL can be optimal with SQL Server, rather than OR'ing them:

UPDATE MAIN SET save = 'yes'
FROM  temp_table MAIN
WHERE save IS NULL AND EXISTS (
    --parent in the temp table but children are not
    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)
    UNION ALL
    --children are in the temp table but parent is not
    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)) 

Or, trimming it down a bit, and supporting SQL dialects that don't allow FROM with UPDATE:

UPDATE temp_table SET save = 'yes'
WHERE save IS NULL AND acct_id IN (
    --parent in the temp table but children are not
    SELECT acct.parent_acct_id
    FROM acct
    WHERE acct.acct_id NOT IN (SELECT temp.acct_id FROM temp_table temp)
    UNION ALL
    --children are in the temp table but parent is not
    SELECT acct.acct_ID
    FROM acct
    WHERE acct.parent_acct_id NOT IN (SELECT temp.acct_id FROM temp_table temp))

Obviously, trimming the code doesn't make the query run more quickly, although conversely, I'd expect this to perform the same, even producing an identical plan (and less code can be virtue in itself, so long as it is no less readable). However, it does assume temp_table.acct_id isn't nullable; if it is, either put the NOT EXISTS back in the nested part, or add explicit IS NOT NULL checks - (SELECT temp.acct_id FROM temp_table temp WHERE temp.acct_id IS NOT NULL).