sql oracle - update using select

Question

I'm having problems updating a column in one of my tables. I have a table called ORDERS, I added a new column to the table that represents the total cost of an order. I get the total cost by using a query that calculates the cost. Now I'm trying to use update using that select query to fill that column in the table. This is what I have:

update ORDERS
set TOTAL_COST = (
select sum((p.COST*i.QUANTITY)*(1-o.DISCOUNT))+delivery(o.DELIVERY) as TOTAL_COST
from PRODUCT p, ITEM i, ORDERS o
where p.ID_PRODUCT = i.ID_PRODUCT and i.ID_ORDER = o.ID_ORDER
group by o.ID_ORDER, o.DISCOUNT, o.DATE, o.DELIVERY);

My query returns the total cost of every order and that is what I want to have in my table. I get an "single-row subquery returns more than one row" error. I don't know what I'm doing wrong, any suggestions??

Codo · Accepted Answer · 2012-12-04 07:28:14Z

2

In order for your UPDATE to work, the subquery (SELECT SUM...) needs to return a single row for each order. But according to the error message, it returns more than one row for certain.

The main problem certainly is that there's no predicate that would relate the current row to be update to the subquery. So you at least need to add something like this to the subquery:

o.ID_ORDER = ORDERS.ID_ORDER

Furthermore, the GROUP BY clause tends to produce more than one row for the UPDATE. It needs to be reduced to o.ID_ORDER only.

The third problem is the delivery cost, which should be summed but added at the end. So they have to be moved out of the subquery:

update ORDERS
set TOTAL_COST = (
  select sum((p.COST*i.QUANTITY)*(1-o.DISCOUNT)) as TOTAL_COST
  from PRODUCT p, ITEM i, ORDERS o
  where o.ID_ORDER = ORDERS.ID_ORDER and p.ID_PRODUCT = i.ID_PRODUCT and i.ID_ORDER = o.ID_ORDER
  group by o.ID_ORDER
) + delivery(DELIVERY);

edited Dec 4, 2012 at 7:28

answered Dec 4, 2012 at 6:50

Codo

79.7k18 gold badges176 silver badges218 bronze badges

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

2 Comments

Codo Over a year ago

I've made a further fix and moved the delivery cost out of the subquery.

zkranc Over a year ago

thanks for your help, but I don't think the problem is can be solved it this way, I'm better of just creating a new table

Collectives™ on Stack Overflow

sql oracle - update using select

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related