CODEWITHSUNDEEP
sqloracle-databaseplsql
Luron
Luron

Reputation: 1187

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

Upvotes: 0

Views: 983

Answers (4)

Pablo Lascano
Pablo Lascano

Reputation: 662

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

Upvotes: 1

Luron
Luron

Reputation: 1187

figured it out. thanks anyway. 

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

Upvotes: 0

TonyP
TonyP

Reputation: 5873

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

where PNO is not null

Upvotes: 0

Parris Varney
Parris Varney

Reputation: 11478

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

Upvotes: 0

Related Questions