July 12, 2006

Set-Based Updates

Continuing with last week's topic, how do we efficiently insert rows into a table or update a table? (The examples here will use Oracle syntax.)

Using a procedural language such as PeopleCode or SQR, it's easy to build and insert one row at a time. There is nothing wrong with this and it works well. However, if you are inserting thousands of rows, this method can be inefficent. It's often better to select the necessary data and insert it into the table with just one SQL statement:

  INSERT INTO PS_MY_TABLE
  (EMPLID
  ,NAME
  ,FLAG
  ) SELECT
   J.EMPLID
  ,N.NAME
  ,'Y'
  FROM  PS_JOB  J
  ...
  WHERE
  ...

Not only is this structure efficient, but it also allows you to use constants and data from multiple tables. Note that in most cases you should list the field (column) names as shown in this example, just in case the order of the fields has changed.

We can also update one table from another table:

  UPDATE PS_MY_TABLE
  SET FIELD1 = (SELECT ... FROM [long complicated join conditions])
    , FIELD2 = (SELECT ... FROM [long complicated join conditions])
  WHERE
  ...

This works, but it can be greatly simplified if the various join conditions are the same for each field:

  UPDATE PS_MY_TABLE
  SET    (FIELD1, FIELD2) =
  (SELECT FIELD1, FIELD2 FROM [long complicated join conditions])
  WHERE
  ...

Now, updating using either of these methods can pose the danger of putting NULLs into a field. Recall that character and numeric fields in a PeopleSoft database cannot contain NULL values. There are at least three ways of avoiding this (ignoring procedural solutions such as SQR or PL/SQL). First, we could repeat the SELECT as an EXISTS condition:

  UPDATE PS_MY_TABLE
  SET    (FIELD1, FIELD2) =
  (SELECT FIELD1, FIELD2 FROM [long complicated join conditions])
  WHERE EXISTS
  (SELECT 1 FROM [long complicated join conditions])
  AND
  ...

Another way is to use Oracle functions such as NVL(), DECODE(), COALESCE() and CASE to detect NULL results and deal with them. (This tends to be unnecessary with SQL Server, which updates only the rows corresponding to rows actually found by the SELECT.)

  SET FIELD1 = NVL((SELECT ... FROM ... WHERE ...), ' ')

This post has run a little long, so I'll defer discussion of updating through views to next week. This method is the third way of updating a table from multiple sources while dealing with the NULL problem.

Until next time...