Updatable view or other idea - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updatable view or other idea

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-11-06 : 13:02:21

HI all,

I got new field in my t_product_option

i added a quantity numeric field
now i got a char field
that show
1 gram
5 gram

that why created the new field
so i can add
1
5
instead

but now i want, to update my product options
i cannot do automatic to many different options.

so i wanted to do it manual, i thought of a updatable view.
I need to add the productname (T_product_main) so i can see what the option is referring to.

i made a view:

CREATE VIEW [dbo].[V_product_option_numeric]
AS
SELECT dbo.T_Product_Main.ProductName, dbo.T_Product_Main.id, dbo.T_Product_Option.Quantity, dbo.T_Product_Option.QuantityNumeric, dbo.T_Product_Option.id AS optionid
FROM dbo.T_Product_Main INNER JOIN
dbo.T_Product_Option ON dbo.T_Product_Main.id = dbo.T_Product_Option.ProductID
WHERE (dbo.T_Product_Main.Active = 1) AND (dbo.T_Product_Option.Stock = 1)

But i only need to update only table T_Product_Option.

i get the error

Or in SQL management studio

tnx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-11-07 : 10:28:16

this?

update po
set QuantityNumeric = replace(po.Quantity,' gram','')
from dbo.T_Product_Main pm
INNER JOIN dbo.T_Product_Option po ON pm.id = po.ProductID
WHERE (pm.Active = 1) AND (po.Stock = 1)

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources