Update Stored Procedure - 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 2000 Forums

Transact-SQL (2000)

Update Stored Procedure

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-03 : 13:06:41

Does anybody have idea how I can transform this Access query into SQL Server procedure?

UPDATE Inventory INNER JOIN POSInvoiceLineitems ON [Inventory].[Item Number]=[POSInvoiceLineitems].[Item Number] SET Inventory.[Quantity in Stock] = [Inventory].[Quantity in Stock]-[POSInvoiceLineitems].[Quantity]
WHERE ((([POSInvoiceLineitems].[Invoice Number])=[Forms]![Form1]![Text8]));

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-03 : 13:14:05

create proc x
@Text8 varchar(20)
as

UPDATE Inventory
SET Inventory.[Quantity in Stock] = [Inventory].[Quantity in Stock]-[POSInvoiceLineitems].[Quantity]
from Inventory
INNER JOIN POSInvoiceLineitems
ON [Inventory].[Item Number]=[POSInvoiceLineitems].[Item Number]
WHERE [POSInvoiceLineitems].[Invoice Number] = @Text8
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-03 : 13:15:46

Thanks budy, I am going to try this.

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-03 : 13:22:50

Unfortunatelly something is wrong.
There is error 21037...not valid TSql statement

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-03 : 14:20:54

I tried this and I have error message "Incorrect syntax near word "INNER"...

Help!!!

CREATE PROCEDURE UpdateInventory
@myinvoice varchar(20)
AS
SET NOCOUNT ON
UPDATE Inventory
INNER JOIN POSInvoiceLineitems
ON Inventory.[Item Number] = POSInvoiceLineitems.[Item Number]
SET Inventory.[Quantity in Stock] = Inventory.[Quantity in Stock]-POSInvoiceLineitems.[Quantity]
WHERE POSInvoiceLineitems.[Invoice Number]= @myinvoice
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-03 : 14:52:35

If you are using a JOIN, then you need the FROM in the UPDATE statement. Your SET is in the wrong place:

UPDATE Inventory
SET Inventory.[Quantity in Stock] = Inventory.[Quantity in Stock]-POSInvoiceLineitems.[Quantity]
FROM Inventory
INNER JOIN POSInvoiceLineitems
ON Inventory.[Item Number] = POSInvoiceLineitems.[Item Number]
WHERE POSInvoiceLineitems.[Invoice Number]= @myinvoice

The above compiles fine in Query Analyzer.

Tara

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-03 : 15:16:50

This is great.I do not have any error message by now. I am going to try execute now from aplication to see how is working.
Thank you very much!

CREATE PROCEDURE UpdateInventory
@myinvoice varchar(20)
AS
SET NOCOUNT ON
UPDATE Inventory
SET Inventory.[Quantity in Stock] = Inventory.[Quantity in Stock]-POSInvoiceLineitems.[Quantity]
FROM Inventory
INNER JOIN POSInvoiceLineitems
ON Inventory.[Item Number] = POSInvoiceLineitems.[Item Number]
WHERE POSInvoiceLineitems.[Invoice Number]= @myinvoice

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-04 : 11:18:51

It's working with execution but I have one problem.
If I have two same records in POSInvoiceLineitems table I am updating only first one.
This is example of POSInvoiceLineitems table

InvNum ItemNum Qty
---------------------------
22 10 2
22 20 3
22 10 4

If I send parameter InvNum 22 I am updating ItemNum 10 just with 2 and ItemNum 20 with 3. There is no updating ItemNum 10 with 4.
I understand I need Summirize Data now, but I do not know how I can do that.Please help if you have idea.

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-05 : 10:42:52

I am still looking for help!!!

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-06 : 13:26:13

Solved!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32312

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources