Python241820 - 1 year ago 65
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!

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)
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download