December 6, 2007 at 5:40 am
Hello,
I have use this query to update my field.
UPDATE Orders
SET total_order_price = total_pizza_price
FROM Pizzas_Ordered PO, Orders O
WHERE PO.order_id = O.order_id
But this query will update only first value from total_pizza_price. For example,
The tables are
Pizzas_Ordered
order_id total_pizza_price
1 9
.
.
20 8
.
.
33 2
.
.
47 5
When using query
Orders
order_id total_order_price
1 9
.
.
20 9
.
.
33 9
.
.
47 9
There are only 9 in the total_order_price. Can i update this all value in total_pizza_price to total_order_price? Thank in advance!!
December 6, 2007 at 6:09 am
Hello again,
I have try this query
UPDATE Orders
SET total_order_price =
(SELECT PO.total_pizza_price
FROM Pizzas_Ordered AS PO INNER JOIN
Orders AS O ON PO.order_id = O.order_id)
But still no luck. This time it said "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression." so, what an expression that can I use in this case? Please advice!
December 6, 2007 at 6:35 am
Assuming a lot (like that you are trying to add up the order amounts in your Pizzas ordered table and put the value to the Orders table's total_order_price), you could use a query like:
UPDATE Orders
SET total_order_price = ( SELECT SUM(PO.total_pizza_price)
FROM Pizzas_Ordered AS PO
WHERE PO.order_id = o.order_id
)
FROM Orders o
Regards,
Andras
December 14, 2007 at 11:01 am
You need to change the first line of the first update command to "Update O" instead of "Update Orders". You used a table alias in your update query, and you need to use the same one in the update statement.
Try that, see if it solves it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2007 at 1:32 pm
The update statement can be well written as:
UPDATE Orders
SET total_order_price =
FROM Orders o
INNER JOIN
( SELECT PO.order_id, SUM(PO.total_pizza_price) as sum_price FROM Pizzas_Ordered PO
GROUP BY PO.order_id)
tp on (tp.order_id = o.order_id)
Hope this works for you....
Thanks
Pawan
December 14, 2007 at 1:33 pm
The update statement can be well written as:
UPDATE Orders
SET total_order_price = tp.sum_price
FROM Orders o
INNER JOIN
( SELECT PO.order_id, SUM(PO.total_pizza_price) as sum_price FROM Pizzas_Ordered PO
GROUP BY PO.order_id)
tp on (tp.order_id = o.order_id)
Hope this works for you....
Thanks
Pawan
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply