SQL Script. Updating a column in a table

I am wondering what I am doing wrong. I have 2 tables used in this problem: ODETAILS and PARTS.

ODETAILS has the following columns: ONO, PNO, QTY, COST

PARTS has the following columns: PNO, PNAME, QOH, PRICE, OLEVEL

I am trying to update the column COST i开发者_如何学编程n ODETAILS so that the COST = QTY * PRICE

PRICE is found in PARTS. But how can I connect those two tables. I am having a brain fart because this is what I tried:

UPDATE ODETAILS
SET COST = QTY * 
    (SELECT PRICE
    FROM PARTS
    WHERE PNO =
        (SELECT PNO
        FROM ODETAILS
        WHERE NOT PNO= NULL
        )
    )
WHERE NOT PNO = NULL;

---

I think the second subquery is wrong becasue it can give you multiple rows and it is unnecesary:

update odetails
set cost = qty * (select price from parts where parts.pno = odetails.pno)
where pno is not null

---

In mysql at least, you can't reopen the table you're updating in a subselect. Try this:

UPDATE ODETAILS o
JOIN   PARTS p
ON     o.PNO = p.PNO
SET    o.COST = o.QTY * p.PRICE

---

Depending on Database settings (null yield null) , you can not compare with equal, not equal operator. Use

where PNO is not null

---

figured it out. thanks anyway.

UPDATE ODETAILS
SET COST = QTY * (
   SELECT PRICE
   FROM PARTS
   WHERE (PARTS.PNO=ODETAILS.PNO)
);