I have Two files FILE1 and FILE2. File1 has no duplicate records but file2 have duplicate records. The records in the file2 which are duplicate should sum up the Quantity( field2 in file2) and the sumup value should be updated in the File1.
File1= itnum, qtyavl
File2= itmnum, qtybln
Here i have tried using MERGE INTO function, it works perfectly but i don't want to use it because MERGE fuctionality can only be used from 7.1 version of ibmi. I want to write the statement without using MERGE.
MERGE INTO file1 AS T
USING (SELECT itmnum, sum(qtybln) AS balance
FROM file2 GROUP BY itmnum) AS S
ON S.itmnum = T.itnum and qtyavl <> s.balance
WHEN MATCHED THEN UPDATE SET qtyavl = s.balance
Since 7.1 has been out of service for years, it is not unreasonable to require v7.1. Even v7.2 is effectively out of service, and only receiving bug fixes as of right now. But:
update file1 t
set qtyval = (select sum(qtybln) from file2 where itmnum = t.itnum)
where itmnum in (select itmnum from file2)
should work for you at any release. Note that the where clause in the update statement only affects which records to update. Unless qtyval can contain a null value, you only want to update the rows where select sum(qtybln) from file2 where itmnum = t.itmnum is going to return a non-null value. Or you could wrap the sub-select in a coalesce(), and assign a value of 0 where the sub-select returns a null.
Edit: If you only want to update the rowws where the QTYVAL needs to be changed, use this
update file1 t
set qtyval = (select sum(qtybln) from file2 where itmnum = t.itnum)
where itmnum in (select itmnum from file2)
and qtyval <> (select sum(qtybln) from file2 where itmnum = t.itnum)
In DB2 you cannot combine the row filter (where clause) with the values to be inserted in a single clause. This leads to what may seem like duplication, but the SQL optimizer is good at rewriting the SQL for performance.