Python241820 Python241820 - 6 months ago 10
SQL Question

How to insert the sum of two tables in Oracle?

I create the following tables:

create table AandB (
code varchar(9),
total number,
CONSTRAINT pk_code PRIMARY KEY (code)
);

create table A (
codeA varchar(9),
numberA number,
CONSTRAINT pk_codeA PRIMARY KEY (codeA)
);

create table B (
codeB varchar(9),
numberB number,
CONSTRAINT pk_codeB PRIMARY KEY (codeB)
);


I inserted the following datas:

insert into AandB(code) values('0x1');
insert into AandB(code) values('0x2');
insert into AandB(code) values('0x3');

insert into A(codeA, numberA) values('0x1',5);
insert into A(codeA, numberA) values('0x2',6);
insert into A(codeA, numberA) values('0x3',1);

insert into B(codeB, numberB) values('0x1',8);
insert into B(codeB, numberB) values('0x2',10);
insert into B(codeB, numberB) values('0x3',12);


The question is: how to insert numberA+numberB in the column "total" of the table "AandB", ie:


0X1 = 13

0X2 = 16

0X3 = 13


Thanks for any suggestions or answers, have a nice day!

Answer

Well, just update the AandB table according to a join between A and B

UPDATE AandB ab
SET ab.total = (SELECT a.numberA + b.numberB
                FROM A
                INNER JOIN B
                 ON(a.codeA = b.codeB)
                WHERE a.codeA = ab.code)

If A and B table won't necessarily contain all of AandB codes , that you should add a where clause to filter them:

UPDATE AandB ab
SET ab.total = (SELECT a.numberA + b.numberB
                FROM A
                INNER JOIN B
                 ON(a.codeA = b.codeB)
                WHERE a.codeA = ab.code)
WHERE EXISTS(SELECT 1 FROM A
             INNER JOIN B
              ON(a.codeA = b.codeB)
             WHERE a.codeA = ab.code)