SQL Script. Updating a column in a table

Question

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;

Florin Ghita · Accepted Answer · 2012-05-04 13:02:04Z

1

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

edited May 4, 2012 at 13:02

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

answered Feb 23, 2011 at 17:31

Pablo Lascano

6626 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parris Varney · Accepted Answer · 2011-02-23 17:20:25Z

0

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

answered Feb 23, 2011 at 17:20

Parris Varney

11.5k12 gold badges49 silver badges77 bronze badges

Comments

TonyP · Accepted Answer · 2011-02-23 17:21:38Z

0

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

where PNO is not null

answered Feb 23, 2011 at 17:21

TonyP

5,89314 gold badges65 silver badges101 bronze badges

Comments

Luron · Accepted Answer · 2011-02-23 17:48:38Z

0

figured it out. thanks anyway.

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

answered Feb 23, 2011 at 17:48

Luron

1,1875 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

SQL Script. Updating a column in a table

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related