Update multiple fields by summing with fields from another file

[Vectorspace]

FileA
aakey1
aanum1
aanum2
aanum3
aanum4
aanum5

FileB
bbkey1
bbkey2
bbnum1
bbnum2
bbnum3
bbnum4
bbnum5

I need to subtract from FileA fields aanum1-aanum5, the vaules from FileB fields bbnum1-bbnum5, for a specific matching FileB record

The only way I can think of to do this is:

update FileA f1
set
aanum1 = aanum1 - (select bbnum1 from FileB f2 where f1.aakey1 = f2.bbkey1 and f2.bbkey2 = something),
aanum2 = aanum2 - (select bbnum2 from FileB f2 where f1.aakey1 = f2.bbkey1 and f2.bbkey2 = something),
aanum3 = aanum3 - (select bbnum3 from FileB f2 where f1.aakey1 = f2.bbkey1 and f2.bbkey2 = something),
aanum4 = aanum4 - (select bbnum4 from FileB f2 where f1.aakey1 = f2.bbkey1 and f2.bbkey2 = something),
aanum5 = aanum5 - (select bbnum5 from FileB f2 where f1.aakey1 = f2.bbkey1 and f2.bbkey2 = something)
where exists (select 1 from FileB f2 where f1.aakey1 = f2.bbkey1 and f2.bbkey2 = something)

Is there a way to do this without specifying the FileB select statement 6 times?

I can't use MATCH as we are only on v6.1, so MATCH is not supported

[Jim_IT]

Re: Update multiple fields by summing with fields from another file

VectorSpace,

It is possible to do it with one select.

create table QTEMP.filea (aakey1, aanum1, aanum2, aanum3, aanum4, aanum5)
As (Select * From (Values (1, 5, 6, 7, 8, 9), (2, 4, 5, 6, 7, 8)) as A
) With Data

create table QTEMP.fileb (bbkey1, bbkey2, bbnum1, bbnum2, bbnum3, bbnum4, bbnum5)
As (Select * From (Values (1, 1, 2, 2, 2, 2, 2)) as A
) With Data


update QTEMP.filea
set (aanum1, aanum2, aanum3, aanum4, aanum5) =
(Select aanum1 - bbnum1, aanum2 - bbnum2, aanum3 - bbnum3, aanum4 - bbnum4, aanum5 - bbnum5
from
QTEMP.fileb
Where
aakey1 = bbkey1)
where
exists (Select 1 from QTEMP.fileb Where aakey1 = bbkey1)

Select
*
From
QTEMP.filea

Output:

AAKEY1	AANUM1	AANUM2	AANUM3	AANUM4	AANUM5
1	3	4	5	6	7
2	4	5	6	7	8

Jim

[Vectorspace]

Re: Update multiple fields by summing with fields from another file

Fantastic, thanks!

Coincidentally - I found out just an hour ago that our development box is getting an upgrade to v7.2 at the end of the month!