SQL Update coding best practice

[sa0309]

I have an update to do on a Oracle 11g table and am looking for coding best practices.

Example:

UPDATE TABLE1
SET TRANSACTION_CODE = '3001', UPDATES = 'Y' WHERE TECHNICAL_DESCRIPTION = 'EDI adjustment credit - Sequestration - reduced fe' AND TRANSACTION_AMOUNT > 0 AND TRANSACTION_CODE =' ';
UPDATE TABLE1
SET TRANSACTION_CODE = '3801', UPDATES = 'Y' WHERE TECHNICAL_DESCRIPTION = 'Late Charge Processing - Late Charge Reversal' AND TRANSACTION_AMOUNT > 0 AND TRANSACTION_CODE =' ';
UPDATE TABLE1
SET TRANSACTION_CODE = '1704', UPDATES = 'Y' WHERE TECHNICAL_DESCRIPTION = 'Collection agency payment - Bad Debt Recovery' AND TRANSACTION_AMOUNT > 0 AND TRANSACTION_CODE =' ';

Should this be combined into 1 update statement using DECODE, IF THEN ELSE, etc. or simply update using the individual updates?

Any suggestions would be appreciated.

Thanks in advance.

 

[johnherman]

It depends. If there is an index on TECHNICAL_DESCRIPTION, TRANSACTION_AMOUNT, and TRANSACTION_CODE then it will not matter. If there are no indexes then the UPDATE will do a full table scan for each UPDATE statement, so the DECODE will improve performance. If there are indexes on one or two of the columns in the WHERE clause, it will be hard to tell which will have better performance without explicit testing. I would use the DECODE and the IF/THEN/ELSE. Then, put the condition that is expected to occur most frequently in the THEN after the IF phrase, as it will hit that one first and then not fall into the rest of the decision logic. In your case, if the TECHNICAL_DESCRIPTION 'EDI adjustment credit - Sequestration - reduced fe' is expected to be more frequent than the other two, list it first.

==================================
adaptive uber info galaxies (bigger, better, faster than agile big data clouds)

 

[LKBrwnDBA]

Perhaps something like this? (not tested)

UPDATE ( SELECT T1.Transaction_Code
              , T1.Technical_Description
              , T1.ROWID Row_Id
              , D1.Code D1_Code
              , D1.Descr D1_Desc
           FROM Table1 T1
              , ( SELECT '3001' Code, 'EDI adjustment credit - Sequestration - reduced fe' Descr FROM DUAL UNION
                  SELECT '3801', 'Late Charge Processing - Late Charge Reversal' FROM DUAL UNION
                  SELECT '1704', 'Collection agency payment - Bad Debt Recovery' FROM DUAL ) D1 )
   SET Transaction_Code = D1_Code, Updates = 'Y'
 WHERE Transaction_Amount > 0
   AND Transaction_Code = ' '
   AND Technical_Description = D1_Descr;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb