jump to navigation

Faster SQL Updates using ROWID April 7, 2007

Posted by Duncan in Oracle, PeopleSoft, SQL.
trackback

Most UPDATE statements get written using the technique:

UPDATE <recname>
   SET <fieldname> = <field, expression or statement>
 WHERE <where clause>

However, if the value that you’re setting the field to is a fairly complex SQL statement of its own there may be a better way (particularly if you have an Oracle DB). You can take advantage of the fact that ROWID is the fastest way to locate a row (faster even than the primary key). Run the first bit of SQL to select the rowid of the row to update and the value to update it to, and then the second bit to run the updates. This can be done either as 2 SQL blocks or with an App Engine Step containing DoSelect and SQL actions.

The Select Statement

SELECT rowidtochar(rowid), <field, expression or statement>
  FROM <recname>
 WHERE <where clause>

The Update Statement

UPDATE <recname>
   SET <fieldname> = <value>
 WHERE rowidtochar(rowid) = rowidtochar(rowid)

If you are doing this inside an App Engine, you’d need to add the rowid and value fields to the state record, and put a %Select at the start of the SELECT statement. Then you can refer to the value and the rowid in the UPDATE statement using %Bind.

Example

The Select Statement – Inside a DoSelect Action

%Select(ROWNAME1, JRNL_TOTAL_LINES, JRNL_TOTAL_DEBITS, JRNL_TOT_CREDITS)
SELECT rowidtochar(H.rowid) row_id
     , (SELECT MAX(L1.JOURNAL_LINE)
          FROM PS_JRNL_LN L1
         WHERE L1.business_unit = h.business_unit
           AND L1.journal_id = h.journal_id
           AND L1.journal_date = h.journal_date
           AND L1.unpost_seq = h.unpost_seq) MAX
     , NVL((SELECT SUM(L2.MONETARY_AMOUNT)
              FROM PS_JRNL_LN L2
             WHERE L2.business_unit = h.business_unit
               AND L2.journal_id = h.journal_id
               AND L2.journal_date = h.journal_date
               AND L2.unpost_seq = h.unpost_seq
               AND L2.MONETARY_AMOUNT > 0),0) SUM_DEBITS
     , NVL((SELECT SUM(L3.MONETARY_AMOUNT)
              FROM PS_JRNL_LN L3
             WHERE L3.business_unit = h.business_unit
               AND L3.journal_id = h.journal_id
               AND L3.journal_date = h.journal_date
               AND L3.unpost_seq = h.unpost_seq
               AND L3.MONETARY_AMOUNT < 0),0) SUM_CREDITS
  FROM PS_JRNL_HEADER H
 WHERE (H.JOURNAL_ID LIKE 'OBAL%' OR H.JOURNAL_ID LIKE 'CBAL%')

The Update Statement – Inside a SQL Action

UPDATE PS_JRNL_HEADER
   SET JRNL_TOTAL_LINES = %Bind(JRNL_TOTAL_LINES)
     , JRNL_TOTAL_DEBITS = %Bind(JRNL_TOTAL_DEBITS)
     , JRNL_TOT_CREDITS = %Bind(JRNL_TOT_CREDITS)
 WHERE rowid = %Bind(ROWNAME1)

Advertisements
%d bloggers like this: