1

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)
);
Improve this question
8
  • What is your expected output? Commented Nov 3, 2017 at 14:46
  • I expect x to be 30 and y to be 60 there! Commented Nov 3, 2017 at 14:48
  • Does the total_invoice_price column exist in your table already? What data type is it? It should be NUMBER, but then it couldn't hold 'x' and 'y', so I am confused. Commented Nov 3, 2017 at 14:50
  • Aaaah sorry, x and y were just placeholders!!! I'll edit it! Commented Nov 3, 2017 at 14:51
  • Better to replace them with NULL, if that's what you really have in the table before the UPDATE. Commented Nov 3, 2017 at 14:51

2 Answers 2

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

20 Comments

@mathguy. I would not use words like really pisses me off in any case if my solution is accepted or not. I gave my answer and if worked fine or not should be upto OP. Its not necessary that everthing we answer is always accepted. Nothing to feel personal.
@mathguy Whatever be the case. I guess if you are so much sensative then this is not the platform to express it. Good day.
@mathguy The reason why we have mutilple options open for a Question is to look for an alternate if something is not working rather digging the into the solution which is giving error at user end. I see nothing wrong in jumping to another solution
man I ran EXACTLY the query you sent me, instead of asking maybe for more details or for me to clarify or look for some other errors I might've done you decided to PROVE that your answer was 100% right and accuse me of running it differently. I just ctrl c + ctrl v, it gave me that error which I have no idea why happened! Don't be so agressive, people here are learning, I'm learning, I don't post here that often, I'm not that good with SQL, I'm trying to help you help me by providing as much info as I can.
@mathguy geez, you're the one arguing. They're not exactly like that, they have more columns and more data in them, but the names and everything else is the same!
|
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>
Improve this answer

5 Comments

So, I tried running this and it gives me the following: SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table 01779. 00000 - "cannot modify a column which maps to a non key-preserved table" *Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. *Action: Modify the underlying base tables directly.
@brightpants - Then you did not use the solution EXACTLY as written. I tested on my system (by creating the tables and then running the solution exactly as written) and it work perfectly fine. Show the EXACT update statement you ran.
edited with my table creation. the update statement I ran follows as the exact same!
@brightpants - Thank you for following up, but I will abandon this thread. I seem to have upset too many people already.
Because I can totally understand what you were doing, whereas I have much more difficulty getting the merge solution

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.