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;

/

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 );

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;

/

What errors?

Post the complete procedure and error messages...

I got it to work. Thanks.