Link to home
Start Free TrialLog in
Avatar of Delerium1978
Delerium1978Flag for United Kingdom of Great Britain and Northern Ireland

asked on

AS400 SQL - Updating a file based on contents of a driver file

Hi All

I'm trying to generate 2 SQL strings to run in an AS400 STRSQL command. First one is to update existing records based on the contents of a driver file and the second one is to actually insert new records based on a driver file. I've always struggled with the correct syntax and really want the procedures to be very resource efficient (There's a lot of records!).

So <MASTERFILE1> holds records which need to be updated with field data contained in <DRIVER1>. An example of file format for both files would be:

Field 1 (Key field)
Field 2 (Key field)
Field 3 (Key field)
Field 4 (Key field)
Field 5 (Key field)
Field 6 (data to update)
Field 7 (data to update)

If you were tackling this, would you simply say:

UPDATE MASTERFILE1 SET Field6=DRIVER1.Field6 WHERE ...... i got lost at this bit :(


The second query is simply to insert all records from DRIVER2 file (Same formats as before) into MASTERFILE1. I'm really unsure about how to even start on this.

My knowledge of SQL is basic in terms of, I can do select statements, nested selects etc but when I need to update records based on others... i'm lost :(

Any pointers?

James
Avatar of RehanYousaf
RehanYousaf
Flag of United Kingdom of Great Britain and Northern Ireland image

Not a problem ... just provide simple syntax (or plain english) what conditions you want in where clause

UPDATE MASTERFILE1
SET Field6=DRIVER1.Field6, Field7=DRIVER1.Field7
WHERE ......
Avatar of Delerium1978

ASKER

Hi Yousaf, Not sure I follow.

For every record in the DRIVER1 file, I need to update the corresponding record in the MASTERFILE1.

In query 2, for every record in DRIVER2 file, i need to insert a record into MASTERFILE1.
Avatar of Member_2_276102
Member_2_276102

An example of file format for both files would be...

I don't understand the format. Is each line a separate record in the driver file? Is it always FIELD6 and FIELD7 that will be updated?

Tom
Ah ok, I was trying to generalise it hoping it would make it easier.

MASTERFILE1

EOCICD
EOB6CD
EOBDCD
EOBUCD
EOBODT
EOPNNB
EOA5VA

DRIVER1

EOCICD
EOB6CD
EOBDCD
EOBUCD
EOBODT
EOPNNB
EOA5VA

DRIVER2

EOCICD
EOB6CD
EOBDCD
EOBUCD
EOBODT
EOPNNB
EOA5VA

The first 6 fields in each file identify it as a unique record. The last field is what needs to be amended in the first query. DRIVER2 contains records we just want to insert directly into MASTERFILE1.

Is this any clearer?

James
So what you mean to say is that update field EOA5VA of masterfile1 from EOA5VA field of driver1 where the following fields match for both tables

EOCICD
EOB6CD
EOBDCD
EOBUCD
EOBODT
EOPNNB
SOLUTION
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
if thats the case then you can try the follwoing code

UPDATE 
	m
SET
	m.EOA5VA = d1.EOA5VA
FROM
	masterfile1 m
	INNER JOIN driver1
		ON m.EOCICD = d.EOCICD
			m.EOB6CD = d.EOB6CD
			m.EOBDCD = d.EOBDCD
			m.EOBUCD = d.EOBUCD
			m.EOBODT = d.EOBODT
			m.EOPNNB = d.EOPNNB

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry forgot to put and in join condition

UPDATE 
	m
SET
	m.EOA5VA = d1.EOA5VA
FROM
	masterfile1 m
	INNER JOIN driver1
		ON m.EOCICD = d.EOCICD
			AND m.EOB6CD = d.EOB6CD
			AND m.EOBDCD = d.EOBDCD
			AND m.EOBUCD = d.EOBUCD
			AND m.EOBODT = d.EOBODT
			AND m.EOPNNB = d.EOPNNB

Open in new window

-- That's not right.  This should do it for the first one.

update masterfile m set fld6 =
coalesce((select d1.fld6 from driver1 d1 where
   m.fld1=d.fld1 and
   ...
   m.fld5=d.fld5),m.fld6)

For every row in masterfile, run the SELECT query.   If there is a matching row, the SELECT will return d1.fld6.  If not, it will return NULL.

The coalesce statement returns the first non-null value in the list.  SO if the SELECT returns a value, that is what gets SET to m.fld6.  If the SELECT return null, then the current value (m.fld6) is assigned.

- Gary Patterson
Hi Guys, thanks for the solutions, im in the process of getting a dataset put on our AS400 for me to test a few of these solutions against

Gary - the coalesce function, am i right in assuming this is only needed if there are null values in the join? The statement you've written looks like it is going through the entire masterfile and for each record, testing whether this is in the driver file, if not updating it with its current value, if it is, updating with new value. This seems a very resource intensive approach?

Yousaf - your approach looks to be whats desired as its only going to process those that exactly join, but let me confirm. AS400 SQL isnt full SQL so lets see if it accepts the parameters.

Thanks

James
What is the OS release on your AS/400?

Tom
@Tom - how can i tell that?
FWIW, I actually created the tables and tested that last query I sent you.  It looks like it works.

Coalesce is used in the query I supplied to allow you to set a default value in place of a null.  In this case, the default value is the current value - effectively saying "no change".  

The important thing to understand is that the subquery is repeatedly "called" and executed over an over - one time for each row in the outside query.

So rows that have a "match" between the master and driver table will return a valid value: driver2.fld6.

But rows that don't have a match will return null, so you have to handle the null.  that's why the coalesce function is in there.

So if you ever have the case where there is a "no match" condition, you need the COALESCE.  It is there to handle the "no match found" condition.

Hope that helps.

- Gary
The OS release helps us know how new (or old) the DB2 release is. Each release added features, so we'll know what queries and formats are available.

Using iSeries Navigator, you can right-click a Connection icon and look at Properties.
Using a telnet session, you can run the WRKLICINF command and look at the first listed License Term value. Or you can run DSPDTAARA QSS1MRI and take the first six characters from the displayed value.

There are other ways, but those are usually easiest.

Tom