Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2015
    Posts
    4

    Unanswered: SQL UPDATE SET one column to be equal to another in a sep/related table w/i procedure

    I am trying to create a pl/sql procedure that does the following:
    - create procedure "ex5b_add _quantities" that adds the quantities that have been ordered for each product so far and updates those values for the 'qty_ordered' attribute/column in the product table.
    - for those product that do not yet have an order, the value inserted for the attribute should be zero.
    - at end of processing, display product_id and qty_ordered values.

    I have attached a pic of both tables, showing the qty_ordered how it is null ( the first thing required was to alter the table and add the column - done)

    ** my biggest problem here seems to be how to put the summed quantities in the 'qty_ordered' column of the product table from the order_detail table.
    Everything I try isn't working. The results should show this:

    ID Qty Ordered
    -- -----------
    10010 0
    10011 8
    10012 4
    10013 0
    10014 0
    10015 5
    10016 2
    10017 1
    10018 3
    10019 9
    10020 0
    10021 1

    My select statement shows the above except what is zero is showing null.

    Here is my code:

    Code:
    create or replace procedure ex5b_add_quantities is
    
    cursor qty_cur is 
    
    select product.product_id, sum(order_details.quantity) as quantity
    from order_details right join product 
    on order_details.product_id = product.product_id 
    group by product.product_id
    order by product_id asc;
    
    qty_row qty_cur%rowtype;
    quantity order_details.quantity%type;
    product_id product.product_id%type;
    
    BEGIN
    dbms_output.put_line('ID                        Qty_Ordered');
    dbms_output.put_line('--------                  -------------');
    for qty_row in qty_cur
    loop 
    
    -- update goes here?  
    
    dbms_output.put_line(qty_row.product_id||'                 '||qty_row.quantity);
    
    end loop;
    end;











    Click image for larger version. 

Name:	order_details.JPG 
Views:	2 
Size:	23.5 KB 
ID:	16170Click image for larger version. 

Name:	product_tbl.JPG 
Views:	2 
Size:	73.4 KB 
ID:	16171

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2015
    Posts
    4

    Apologies for not posting OS and oracle info/output msgs - see below

    OS name and version - windows 7 home premium
    Oracle version # - 11.2.0.2.0 - 64bit production - ran in SQL Developer

    Input - code is what was in initial inquiry
    Output of entire code from procedure run:

    Connecting to the database xyz.
    ID Qty_Ordered
    -------- -------------
    10010
    10011 8
    10012 4
    10013
    10014
    10015 5
    10016 2
    10017 1
    10018 3
    10019 9
    10020
    10021 1
    Process exited.
    Disconnecting from the database xyz.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT product.product_id, 
           SUM(Nvl(order_details.quantity, 0)) AS quantity 
    FROM   order_details 
           right join product 
                   ON order_details.product_id = product.product_id 
    GROUP  BY product.product_id 
    ORDER  BY product_id ASC;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2015
    Posts
    4

    Thank you

    I hadn't heard of NVL before today. Looked it up and how it is used.

    Although this gives me the correct output, it is not enough to update the quantities in the product table where I included a picture of.... They still show all null values.

    See below: Click image for larger version. 

Name:	product_tbl.JPG 
Views:	1 
Size:	73.4 KB 
ID:	16172

    The Product table attribute "qty_ordered" has to be updated with those values.

    Might still need an UPDATE statement...
    Last edited by Lorelei7; 02-18-15 at 21:17.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    NULL plus anything equals NULL
    ZERO plus anything equals anything
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2015
    Posts
    4

    Don't understand.

    I am sorry, but that doesn't help me understand how to do the update statement to make the product table attribute 'qty_ordered' have to order_details summed quantities.
    This is three weeks in to my trying to use procedures and pl/sql.

    LR

    In addition, I know this update is supposed to be a simple operation, but I have spent days trying to solve this one problem completely from beginning to end - nearly 24 hours and before an hour ago, I couldn't get anyone to help me, even instructors nor have I found an example of a similar scenario online to try and derive how to do it.
    Last edited by Lorelei7; 02-18-15 at 22:21.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •