SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Updatable view or other idea
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
200 Posts

Posted - 11/06/2013 :  13:02:21  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

Edited by - mike13 on 11/06/2013 13:07:33

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 11/07/2013 :  10:28:16  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000