INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Update coding best practice

SQL Update coding best practice

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

RE: SQL Update coding best practice

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)


RE: SQL Update coding best practice


Perhaps something like this? (not tested)

CODE

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; 
3eyes

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close