0

I have two separate UPDATEs as follows:

UPDATE mytab
SET colA = 1 
WHERE id IN ( SELECT id FROM mytab
              JOIN othertab ON mytab.otid = othertab.id 
              WHERE othertab.Lock = 'SomeLock' AND mytab.colA IS NULL);
UPDATE mytab
SET colB = 2 
WHERE id IN ( SELECT ID FROM mytab
              JOIN othertab ON mytab.otid = othertab.id 
              WHERE othertab.Lock = 'SomeLock' AND mytab.colB IS NULL);

I wish them as one merged UPDATE query. What I did is:

UPDATE mytab
SET colA = 
CASE WHEN colA IS NULL THEN 1 ELSE colA END,
colB = 
CASE WHEN colB IS NULL THEN 2 ELSE colB END 
WHERE id IN ( SELECT id FROM mytab
                  JOIN othertab ON mytab.otid = othertab.id 
                  WHERE othertab.Lock = 'SomeLock');

In general it does what I want on both SQL Server and Oracle. What is concerning me here is the performance of the merged query. In that shape it will update every row that is returned after filtering by predicate. Any better solutions?

  • I'd add .. AND (mytab.colA IS NULL OR mytab.colB IS NULL) to subquery's condition if the amount of rows with NULL in at least one column is low enough (approximately - below ~20%). – Akina Nov 11 '20 at 18:59
  • Are you wanting one query that works on both? If not, please tag only one RDBMS. – Michael Kutz Nov 11 '20 at 19:12
  • @MichaelKutz yes, I need them to work on both Oracle and SQL Server, that is why I tagged it with those two ... – user1337 Nov 11 '20 at 19:15
  • @user1337 Just taking a step back a second, why does it need to be the same query for both RDBMS? (I'm just trying to understand in case there's a better solution that meets your needs.) – J.D. Nov 11 '20 at 19:25
  • Well it doesn't need to be exactly the same query, but would be the best. If there are better solutions specific for those RDBMSes separately it would also be fine for me. – user1337 Nov 11 '20 at 19:28
1

I don't know enough about Oracle to know if the below is valid syntax there or not... or if there are niftier tricks specific to that RDBMS. However, on SQL Server I would do something like this.

/** If you are updating small numbers of rows then this is probably good enough 
    on SQL Server.  Properly indexed tables, etc.
    **/
UPDATE mytab
SET colA = COALESCE(colA, 1)
    , colB = COALESCE(colB, 2)
WHERE EXISTS (SELECT TOP (1) 1 FROM othertab WHERE othertab.id = mytab.otid AND othertab.Lock = 'SomeLock')
    AND (   colA IS NULL
            OR colB IS NULL
            )
            
/** Larger numbers of rows could do better like this.
    **/
UPDATE M
SET colA = COALESCE(colA, 1)
    , colB = COALESCE(colB, 2)
FROM mytab AS M
    INNER JOIN othertab AS O ON O.id = M.otid AND O.Lock = 'SomeLock'
WHERE M.ColA IS NULL
    OR M.ColB IS NULL 
  • For the line from mytab AS M - the AS keyword is not supported in Oracle. Beyond that, the 2nd statement looks better (ie possibly easier to maintain) than my MERGE – Michael Kutz Nov 12 '20 at 8:25
0

If >20% of the rows are being modified, there might be better ways.

Oracle

merge into mytab a
using (
    select c.id
           ,coalesce( c.colA, 1 ) as colA
           ,coalesce( c.colB, 2) as colB
           -- Oracle performance might improve with NVL() instead of coalesce
    from mytab c
      join othertab d on c.otid = d.id
    where d.lock = 'SomeLock'
      and ( c.colA is null or c.colB is null )
) b
on ( a.id = b.id )
when matched then update set
    a.colA = b.colA
    ,a.qoh = b.colB;
  • a /*+ parallel */ hint might help improve further
    • only try it out if the new statement isn't fast enough.
  • DBMS_PARALLEL_EXECUTE could help minimize UNDO space
    • do NOT mix the two

SQL Server

{place code here}

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.