Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    5

    Unanswered: update query in pl/sql

    I want to update a query to populate the column COST with correct values. I am given the information that ODETAILS has a column called COST, whose value is the product of the quantity and price of the product being ordered. I have come up with a solution. Everything compiles fine, but when I go to check to see if the values have been populated, there is nothing under COST. I was wondering what the problem is. PRODUCTS is a table that has a column PRICE.

    DECLARE

    myprod PRODUCTS.PRICE%TYPE;

    CURSOR product_cost IS
    SELECT ODETAILS.COST
    FROM ODETAILS, ORDERS, PRODUCTS
    WHERE PRODUCTS.PNO = ODETAILS.PNO AND ORDERS.ONO = ODETAILS.ONO
    FOR UPDATE OF ODETAILS.COST;


    BEGIN
    FOR product_rec IN product_cost

    LOOP
    UPDATE ODETAILS
    SET ODETAILS.COST = (ODETAILS.QTY * myprod)
    WHERE CURRENT OF product_cost;

    END LOOP;

    END;

    /

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    This looks kinda like homework, but what the heck, try this better:

    UPDATE ODETAILS
    SET COST = (
    SELECT QTY * PRICE
    FROM ORDERS, PRODUCTS
    WHERE PRODUCTS.PNO = ODETAILS.PNO
    AND ORDERS.ONO = ODETAILS.ONO );

  3. #3
    Join Date
    Jul 2003
    Posts
    5

    udpate query in pl/sql

    Based on what you suggested, I tried this, and I'm getting a whole bunch of errors. I don't know what else to do. I declared both price and cost at the top, and I still get errors. This is what I did:

    DECLARE

    myprod PRODUCTS.PRICE%TYPE;

    mycost ODETAISL.COST%TYPE;




    BEGIN


    UPDATE ODETAILS
    SET COST = (SELECT QTY * PRICE
    FROM ORDERS, PRODUCTS, ODETAILS
    WHERE PRODUCTS.PNO = ODETAILS.PNO AND ORDERS.ONO = ODETAILS.ONO);






    END;

    /


    Originally posted by LKBrwn_DBA
    This looks kinda like homework, but what the heck, try this better:

    UPDATE ODETAILS
    SET COST = (
    SELECT QTY * PRICE
    FROM ORDERS, PRODUCTS
    WHERE PRODUCTS.PNO = ODETAILS.PNO
    AND ORDERS.ONO = ODETAILS.ONO );

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    What errors?

    Post the complete procedure and error messages...

  5. #5
    Join Date
    Jul 2003
    Posts
    10
    I got it to work. Thanks.


    Originally posted by LKBrwn_DBA
    What errors?

    Post the complete procedure and error messages...

Posting Permissions

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