| Set-Based Updates | KEVIN RESCHENBERG
07-12-2006 |
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.
|
