Thursday, February 14, 2013

iSeries SQL Update multiple columns


SQL is cool s**t..I love this stuff.... Below SQL uses file created from spreadsheet and updates the min and max fields in the item balance file from one field from uploaded spreadsheet. 

Spreadsheet fields VENDOR, VMFG, CAPITEM, LOC, MINMAX.

update b50files.vinitmb 
set (ifsmin,ifsmax) = (select minmax, minmax from rbryant.upminmax  
where ifcomp = 1 and ifloc = loc and ifitem = capitem) 
where exists (select * from rbryant.upminmax where ifcomp = 1 and ifloc = loc 
                                 and ifitem = capitem)

Updated 04/29/15
I have a task to update several fields in file with calculated amount based on data from a different file. Since I do not write SQL everyday I do not try to remember and use Google. My google search took me to my blog from 2013. Just what I needed and now expanding complexity for future reference.

I need to take the location item price from item price file and reduce by 50% and update cost fields in item balance file. The real trick in my mind is the "exists". I fumbled around and could not get it work until I added the where select exists. 

vinitmb is item balance file and vinpmat is item price file.

ifavg, ifoth and iflst are cost fields being updated.

ifdiv and ifcls are division class fields.

iflcgd, iflcgt and ifuslc are last change fields in case I screw up and need to back out. Of couse all of this is tested with Select statements and then updated test files to make sure.

update r50files.vinitmb 
set iflst = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
ifavg = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
ifoth = (select (j6lprc - (j6lprc * .50)) as ncost from r50files.vinpmat 
where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item),
IFLCGD = 20150429,
IFLCGT = 080000,
IFUSLC = 'SQLRB'
where exists (select * from r50files.vinpmat where j6cmp = ifcomp and ifloc = j6loc and J6PMAT = 'CAP' and ifitem = j6item) 
and ifdiv = 9999 and ifcls = 2 



~Richard


One man's crappy software is another man's full time job.  ~Jessica Gaston

No comments:

Post a Comment