| Generating SQL Updates from SQL | KEVIN RESCHENBERG
01-31-2005 |
It's hard to believe that I've been writing this little blog
for a full year. Time flies! There are several big topics still on
my list. But today I'm going to do just
a quick follow-up to a previous topic.
Last November I
discussed creating SQL using SQL, and showed
an example of
using SQL to generate more SQL that would produce
a listing that could then be used to
create UPDATE statements. In that case, the objective was
to correct bad EMPL_RCD and BENEFIT_RCD_NBR values for one
employee, by finding all rows in all tables that contained
the invalid EMPL_RCD or BENEFIT_RCD_NBR for that employee and
then updating the appropriate field.
The situation came up again recently and I decided to take the next
step and just generate the UPDATEs automatically to eliminate
that manual step. This was very simple to do. The SQL here is
similar to the previous example, except that it generates the
UPDATEs. (This is Oracle syntax.)
SELECT 'UNION SELECT ''UPDATE PS_' || F.RECNAME
|| ' SET '
|| F.FIELDNAME
|| ' = 1 WHERE EMPLID = ''''12345'''' AND '
|| F.FIELDNAME || ' <> 0;'' FROM PS_'
|| R.RECNAME
|| ' WHERE EMPLID = ''12345'' AND '
|| F.FIELDNAME || ' <> 0 HAVING COUNT(*) > 0'
FROM PSRECFIELD F
, PSRECDEFN R
WHERE F.FIELDNAME IN ('EMPL_RCD','BENEFIT_RCD_NBR')
AND R.RECNAME = F.RECNAME
AND R.RECTYPE = 0
AND EXISTS
(SELECT 1
FROM PSRECFIELD
WHERE RECNAME = F.RECNAME
AND FIELDNAME = 'EMPLID'
)
ORDER BY F.RECNAME
This SQL produces hundreds of lines like this:
UNION SELECT
'UPDATE PS_ABSENCE_HIST SET EMPL_RCD = 1
WHERE EMPLID = ''12345'' AND EMPL_RCD <> 0;'
FROM PS_ABSENCE_HIST WHERE EMPLID = '12345'
AND EMPL_RCD <> 0 HAVING COUNT(*) > 0
Remove the very first "UNION" from this output and you have one huge
SELECT statement. Running that statement produces
a few lines like this, where each corresponds to
an actual table containing the employee's data:
UPDATE PS_EMPLOYMENT SET EMPL_RCD = 1
WHERE EMPLID = '12345' AND EMPL_RCD <> 0;
...etc.
And then those statements, in turn, do the actual
update. We have now written SQL to generate SQL
to generate SQL to update data! Wasn't that fun?
Of course, all
of the usual caveats related to subrecords, field
names, and especially testing apply!
|
