SQL for update price

Question

Im doing homework, and cannot figure out exactly what I need to do:

PROBLEM:

• Raise the price of the PARTs 5%.
• Display the current data plus the new price.
• Arrange the output so the quantity on hand descends.

SQL I USED:

UPDATE PART
  set PRICE=PRICE*1.05
ORDER BY ON_HAND DESC;

WHAT I GET IN RETURN:

ERROR at line 3: ORA-00933: SQL command not properly ended

Answer 1

If you just have to display the values you could do something like this:

select item,on_hand,price,(price * 1.05) as new_price
from part
order by on_hand desc;

SQLFiddle

But if you have to update the new price in the table and then display the values, you would have to write two queries, one to update the value and second to display the values.

To update the values:

UPDATE PART set PRICE=PRICE*1.05;

To display the values:

select *
from part
order by on_hand desc;

SQLFiddle

Answer 2

First get the currect values, and calculate the new values:

select PRICE, ON_HAND, PRICE * 1.05 as NEW_PRICE
from PART
order by ON_HAND DESC

Then do the actual update

update PART
set PRICE = PRICE * 1.05

Answer 3

ORDER BY is not a valid clause in an UPDATE query in Oracle SQL (although MySQL does support it). That's why you're getting the error message. Simply remove the clause

It rarely makes a difference exactly which order an UPDATE is performed in anyway.

The rest of your question will require an ORDER BY clause as part of a SELECT statement

Answer 4

UPDATE query cann't be used with ORDER BY.

UPDATE PART set PRICE=PRICE*1.05;  

or

UPDATE PART set PRICE=PRICE*1.05 WHERE some_condition;