![alt text] How to select a value from a table plus a new value that would be inserted to update a table using stored procedure. Find my table bellow. What i want is to select old quantsold + the new Qunatsold to update the same table. Thanks in advance. : /storage/temp/2978-2015-10-26-142405.png
Here is how you could do this with a trigger. Please note this won't work with your sample insert statement because you don't have a row in Inventory_t for 'SNAPA B'. My previous comments about normalization are still valid. This is in serious need of normalization. You also really don't need varchar(max) for customer name. varchar(100) is probably more than reasonable. The max datatype allows for 2GB of data which is multiple copies of Tolstoy's book "War and Peace". Also for a sales system I would recommend using DateTime as the datatype because that way you can track the busy time slots during the day. At any rate here is an example of how you could do this with a trigger. create trigger Sales_t_Insert on Sales_t for insert as set nocount on; update inv set QuantSold = inv.QuantSold + i.Qty from Inventory_T inv join inserted i on i.Item = inv.ItemName; Notice that if you had posted the ddl and sample data originally this would have been answered several hours ago. If you want some help normalizing your data structures I will be happy to point you in the right direction.
I have solve it by my self. Thank you for your guide lines. CREATE TABLE [dbo].[Inventory_T]( [ItemID] [int] IDENTITY(1,1) NOT NULL, [ItemName] [nvarchar](100) NULL, [QuantOnHand] [int] NULL, CONSTRAINT [PK_Inventory_T] PRIMARY KEY CLUSTERED ( [ItemID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Inventory_T] ADD CONSTRAINT [DF_Inventory_T_QuantOnHand] DEFAULT ((0)) FOR [QuantOnHand] GO INSERT INTO Inventory_T VALUES ('Pen','6'); INSERT INTO Inventory_T VALUES ('Book','10'); CREATE TABLE [dbo].[ItemLine_T]( [ITLID] [int] IDENTITY(1,1) NOT NULL, [TransType] [varchar](100) NULL, [ItemName] [varchar](100) NULL, [QtySold] [int] NULL, CONSTRAINT [PK_ItemLine_T] PRIMARY KEY CLUSTERED ( [ITLID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT INTO ItemLine_T VALUES ('Pen','2'); INSERT INTO ItemLine_T VALUES ('Book','5'); CREATE VIEW [dbo].[StockView] AS SELECT dbo.Inventory_T.ItemName AS Item, dbo.Inventory_T.QuantOnHand AS Qty, SUM(dbo.ItemLine_T.QtySold) AS [Qty Sold] FROM dbo.Inventory_T INNER JOIN dbo.ItemLine_T ON dbo.Inventory_T.ItemName = dbo.ItemLine_T.ItemName GROUP BY dbo.Inventory_T.ItemName, dbo.Inventory_T.QuantOnHand GO This is the final query SELECT Item, Qty, [Qty Sold], SUM(Qty - [Qty Sold]) AS [Qty on Hand] FROM dbo.stockview GROUP BY Item, [Qty Sold], Qty