In addition to query and reporting tasks, Sequel is an extremely useful tool for managing the data in your IBM i databases. Sequel's UPDATE, DELETE and INSERT commands are use- ful for cleansing bad data or applying mass changes to files without having to write a program or spend tedious hours manually updating files. These commands may be prompted from a com- mand line.

Sequel is unique in that it allows you to do a 'JOIN UPDATE'. In other words, Sequel allows you to automatically update an IBM i file with data from another IBM i file.

In the example below, we will update the List Price field (LSTPC) in the PARTMAST file with the New Price (NEW_PRICE) field in the NEWPRICE file. The contents of the updated PART- MAST file are shown further down.

PARTMAST File   NEWPRICE File
Product Number Product
Description
List
Price
101 Cycle Body 20.00
102 Rear Wheel 2.75
103 Seat Back 3.25
104 Handle Bar 2.75
105 Front Fork 3.00
106 Wheel Crank 9.00
107 Pedal .75
108 Axle Cap .35
109 Rear Axle .95
110 Spacer Washer .25

 
Product
Number
New
Price
101 25.00
102 3.43
103 4.06
104 3.43
105 3.75
106 11.25
107 .93
108 .43
109 1.18
110 .31

 

PARTMAST-Updated    
Product Number Product
Description
List
Price
101 Cycle Body 25.00
102 Rear Wheel 3.43
103 Seat Back 4.06
104 Handle Bar 3.43
105 Front Fork 3.75
106 Wheel Crank 11.25
107 Pedal .93
108 Axle Cap .43
109 Rear Axle 1.18
110 Spacer Washer .31

 
 

The following UPDATE command was used to accomplish the operation above:

UPDATE SET((LSTPC.1 NEW_PRICE.2))SQL('FROM PARTMAST, NEWPRICE JOIN PRDNO.1 = PRDNO.2')

Note: The file you are updating MUST be the Primary (First Mentioned) file in the SQL statement. The first parameter in the SET clause is the column name (field) to be updated. In this example it is the LSTPC (List Price) field. The second parameter in the SET clause assigns the new value to the column name. In this example we specify that another field, NEW_PRICE, will supply the value. The value parameter may also contain a literal value or a derived character or numeric value depending on your requirements. For instance, you may simply want to multiply LSTPC by a numeric value to come up with a new List Price.

The SQL FROM clause specifies the two database files required for the operation and the JOIN identifies the fields used to join the files together. The SQL statement in this example is fairly basic - all LSTPC fields in the PARTMAST file are updated.

  


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: April 21, 2017