Inner Join with Sum in oracle SQL

Question

I'm using Oracle SQL and I have two tables, invoice and invoice_item.

invoice:

id(pk)  total_invoice_price
1   
2   

invoice_item:

invoice  total_item_price
1        10
1        20
2        25
2        35

I need that total_invoice_price be the sum of every total_item_price where invoice = id.

invoice_item.invoice is a fk that references to invoice.id

The best I could make was in the lines of:

update(
select invoice.total_invoice_price as old, SUM(invoice_item.total_item_price) as total
from invoice
inner join invoice_item
on invoice.id = invoice_item.invoice
) t 
set t.old = t.total;

but it obviously doesn't work.

Tables creation:

create table invoice(
  id                number(5)       not null,
  customer_name     varchar2(50)    not null,
  issue_date        date            not null,
  due_date          date            not null,
  comments          varchar2(50)            ,
  total_invoice_price   number(9)               ,
  constraint pk_invoice
  primary key (id)
);

create table invoice_item(
  id                number(5)       not null,
  product_name      varchar2(50)    not null,
  unit_price        number(9)       not null,
  quantity          number(9)       not null,
  total_item_price  number(9)               ,
  invoice           number(5)       not null,
  constraint pk_invoice_item
  primary key (id),
  constraint fk_invoice_item_invoice
  foreign key (invoice)
  references invoice(id)
);

Answer 1

update
  ( select i.total_invoice_price, x.total_price
    from   invoice i
           join
           (
             select   invoice as id, sum(total_item_price) as total_price
             from     invoice_item
             group by invoice
           ) x
           on i.id = x.id
  )
set total_invoice_price = total_price
;

Comments:

You need to aggregate within the second table, before joining. Then you join by id. In this arrangement, you will never run into issues with "uniqueness" or "primary key" being defined; the only condition that matters is that the id be unique in the "other" table, which in this case is the subquery x. Since it is an aggregation where you group by id, that uniqueness is guaranteed by the very definition of GROUP BY.

Then: It is unfortunate that you have a table invoice and a column (in a different table) also called invoice; the invoice id column should be called something like invoice_id in both tables. In my subquery, I changed the column name (from the second table) from invoice to id, by giving it that alias in the SELECT clause of the subquery.

Further comment: In a comment below this replies, the OP says he ran into an error. That means he didn't use the solution as I wrote it above. Since this is really annoying, I decided to present a full SQL*Plus session to prove that the solution is correct as written.

Create table INVOICE:

SQL> create table invoice ( id, total_invoice_price ) as
  2    select 1, cast(null as number) from dual union all
  3    select 2, null from dual;

Table created.

Elapsed: 00:00:00.01
SQL> select * from invoice;

        ID TOTAL_INVOICE_PRICE
---------- -------------------
         1
         2

2 rows selected.

Create table INVOICE_ITEM:

Elapsed: 00:00:00.00
SQL> create table invoice_item ( invoice, total_item_price ) as
  2    select 1, 10 from dual union all
  3    select 1, 20 from dual union all
  4    select 2, 25 from dual union all
  5    select 2, 35 from dual;

Table created.

Elapsed: 00:00:00.01
SQL> select * from invoice_item;

   INVOICE TOTAL_ITEM_PRICE
---------- ----------------
         1               10
         1               20
         2               25
         2               35

4 rows selected.
Elapsed: 00:00:00.00

UPDATE statement:

SQL> update
  2    ( select i.total_invoice_price, x.total_price
  3      from   invoice i
  4             join
  5             (
  6               select   invoice as id, sum(total_item_price) as total_price
  7               from     invoice_item
  8               group by invoice
  9             ) x
 10             on i.id = x.id
 11    )
 12  set total_invoice_price = total_price
 13  ;

2 rows updated.

Elapsed: 00:00:00.01
SQL> select * from invoice;

        ID TOTAL_INVOICE_PRICE
---------- -------------------
         1                  30
         2                  60

2 rows selected.

Elapsed: 00:00:00.02

Clean-up:

SQL> drop table invoice purge;

Table dropped.

Elapsed: 00:00:00.02
SQL> drop table invoice_item purge;

Table dropped.

Elapsed: 00:00:00.01
SQL>

Answer 2

I would use Merge. See below

MERGE INTO invoice tb1
     USING (  SELECT invoice, SUM (total_item_price) tot_price
                FROM invoice_item
            GROUP BY invoice) tb2
        ON (tb1.id = tb2.invoice)
WHEN MATCHED
THEN
   UPDATE SET tb1.total_invoice_price = tb2.tot_price;