December 6, 2007 at 5:30 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:33 am
Actually, you've got it right. It's going to update alll the rows from one table where the id's match in the second table.
Maybe you phrased the question wrong or I misunderstood. I took your data into a table and it worked just fine to update multiple rows.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2007 at 11:50 am
rabin_panichnok,
I too am a little confused because this statement worked as intended. Did you even test your statement or did you post it before running to verify?
The update statement will update all records where the ids are equal, not just the first in the result set.
I believe you were thinking the where clause would perform like a filter and only update the first record found? Not sure though.
Anyway, the results I got from your statement are as follows:
order_idOrder_price
19
208
332
475
The test data is found below:
declare @PizzaOrder table
(
order_id int,
total_pizza_price int
)
insert into @pizzaorder
select 1, 9 UNION ALL
select 20, 8 UNION ALL
select 33, 2 UNION ALL
select 47, 5
declare @Order table
(
order_id int,
Order_price int
)
insert into @order
select 1, 0 UNION ALL
select 20, 0 UNION ALL
select 33, 0 UNION ALL
select 47, 0
UPDATE @Order
SET Order_price = total_pizza_price
FROM @PizzaOrder PO, @Order O
WHERE PO.order_id = O.order_id
select *
from @Order
December 6, 2007 at 7:47 pm
Thanks all. The problem was sloved. It update only the first value from source table to target table because of I have queried by right-click and select pane SQL. The problem can solve by clicking at New Query and insert that query. It works.
Thanks.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply