Update One File Based on Another File
April 14, 2010 Ted Holt
|
It’s not uncommon to have to update one file based on data found in another file. In fact, it’s a very common requirement, due to the way that data is “normalized” in relational database management systems. When only one field links the two files–the file that is being updated and the file that contains the search criteria–the SQL syntax is simple. But joining on two or more fields requires more challenging syntax. Here’s an example of an update when only one field joins the two files. I add a week to the scheduled ship date of orders of type B customers. update salesordh
set shipdate = shipdate + 7 days
where customerno in
(select customerno
from customers
where type = 'B')
Since the sales order header file and customer master file share only one common field–customer number–a simple IN expression does the trick. But suppose the two files join on two fields–company number and customer number. What then? The traditional method is to use the EXISTS construct. update salesordh as h
set shipdate = shipdate + 7 days
where exists
(select *
from customers as c
where type = 'B'
and c.companyno = h.companyno
and c.customerno = h.customerno)
EXISTS proves true if the SELECT that follows it returns at least one row (record). For each row of the sales order header file, SALESORDH, the system looks to see if there is at least one type B customer with the same company number and customer number in the customer master file. If at least one row is found, the sales order header record is updated. If you are V5R4 or above, you have a simpler option–you can use a row value expression. update salesordh
set shipdate = shipdate + 7 days
where (companyno, customerno) in
(select companyno, customerno
from customers
where type = 'B')
Row value expressions let you compare two lists of values to one another. In this example, the company number-customer number combination of a sales record are compared to the set of all company number-customer order combinations from the type B customers. Thanks to row value expressions, I have jettisoned the non-intuitive EXISTS and returned to the simple IN of the first example!
|
