CODEWITHSUNDEEP
sqloracle-databaseinsert
Python241820
Python241820

Reputation: 1527

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!

Upvotes: 1

Views: 109

Answers (1)

sagi
sagi

Reputation: 40481

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)

Upvotes: 1

Related Questions