I've use an SQL utility on the AS/400 call SEQUEL by ASC, that allows updating of values in one table from values in another joined table. The syntax is something like this:
update set((a.fld1=b.fld1)(a.fld2=b.fld2)) from file1 a join file2 b on a.key1=b.key1 where a.key1='OnlyTheOneIWant'
I no longer have access to the SEQUEL tool, and I'm trying to do the same thing with SQL/400. A similar statement in SQL/400, except with only one field being updated, is like this:
UPDATE file1 a set a.fld1 = (SELECT COALESCE(b.fld1, a.fld1) FROM file2 b WHERE a.key1 = b.key1) where a.key1='OnlyTheOneIWant'
For each value I'm setting in file 1, I need a whole SQL sub-statement for file 2. That's not so bad for just one value, but I need to update 9 columns in one table with values from another, and if this is the only way, it would be easier to write an RPG program.
Does anyone know an easier way to do this with SQL/400, preferably one that resembles my first example, and doesn't require a full SQL statement for each value you set?