0

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.

enter image description here

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,

enter image description here

enter image description here

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!

  • First thought: database agnostic code is a myth. Get rid of the "same SQL must work on both" requirement. 2nd thought: I'd like to see the CREATE TABLE and INSERT statements. – Michael Kutz Nov 16 at 0:14
  • Thanks, I have removed that requirement. Yeah, it doesn't need a same sql work for both. I can have two different versions. – Jasmine Rice Nov 16 at 5:24
  • @MichaelKutz - hmm, on basic stuff like this, if you are maintaining a database application where both SQL Server and Oracle are supported backends, then keeping the SQL common seems a sensible default approach, with separate code only looked into when definitely needed (e.g. material performance issues). In this sort of situation, being RDBMS agnostic is a business decision already made, the problem is to minimise its cost in development and maintenance time. – Chris Rolliston Nov 16 at 12:42
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.