March 2, 2006 at 2:15 pm
Can anyone help me with this query. I have tables products and options. There is a third table, products_options. I want to update the price in the products table if the product has a selected option in the products_options table, with the price corresponding to the option selected.
In the below example, in the products table I want to update the price of productid 1023, from $40 to $45 (price for optionid 101) and the price of productid 1026, from $50 to $65(optionid 105).
How do I do this update in a single update statement. The below update statement is not complete, just to give you an idea what I am trying to do.
UPDATE products
SET price = (SELECT price FROM options, products_options WHERE options.productid = products_options.productid)
WHERE productid = ...?
products (productid, price)
1023, $40
1024, $75
1025, $30
1026, $50
options(optionid, productid, price)
100, 1023, $35
101, 1023, $45
102, 1023, $50
103, 1024, $90
104, 1024, $95
105, 1026, $65
products_options(productid, optionid)
1023, 101
1026, 105
After the update, products table should look like this
products (productid, price)
1023, $45
1024, $75
1025, $30
1026, $65
Thanks.
March 2, 2006 at 2:20 pm
Update P
Set
price = O.price
From Products As P
Inner Join Products_Options As OP
On (OP.ProductID = P.ProductID)
Inner Join Options As O
On (O.OptionID = OP.OptionID And
O.ProductID = OP.ProductID)
March 2, 2006 at 2:31 pm
I have never used updadate statement in this format. Should have looked up BOL. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply