Update A table with a loop through a query

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

General SQL Server Forums

New to SQL Server Programming

Update A table with a loop through a query

Author

Topic

JohnDW
Starting Member

45 Posts

Posted - 2013-09-20 : 09:49:29

Hello,

I want to make a SP to update table Product
with information I get from table Orderdetail.

Create Procedure UpdateVoorraad
§OrderId (int)

As

Select ProductId, Tal From Orderdetail where OrderId = @OrderId

-- this query get info from table orderdetail : ProductId (integer) and Tal (smallint)
-- Tal = Number of Products

-- Here I want to loop through the query above
-- and for each record in the query I want to update
-- table Product.

Update Product Set Product.Voorraad = Product.Voorraad - Tal where ProductId = ProductId

To do this must I make a create a tempory table, store the query result in the table
loop through the table and update table product,
or can I try to create a function without a temporary table.

Can someone help me, or give me an exemple?

Grt,

John

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-20 : 10:00:41

You can do it in a single step like shown below. Are you using Microsoft SQL Server? Some of the syntax you are using does not look like SQL Server syntax

Create Procedure UpdateVoorraad
@OrderId INT 

As

UPDATE p SET
	p.Voorraad = p.Voorraad - o.Tal
FROM
	Product p
	INNER JOIN OrderDetail o ON
		o.ProductId = p.ProductId
WHERE
	o.OrderId = @OrderId;

JohnDW
Starting Member

45 Posts

Posted - 2013-09-20 : 17:33:01

Now I try to get a SP get to work where the value of the column product.voorraad gets changed where the orders.orderdatum (=orders.orderdate) is today.
orderdatum (date of orders) = datetime

I tried the following code:

ALTER PROCEDURE [dbo].[UpdateVoorraad]
@Orderdatum datetime

AS
BEGIN
set @Orderdatum = Getdate()

UPDATE product SET
product.Voorraad = product.Voorraad - orderdetail.Tal
FROM
Product INNER JOIN OrderDetail ON
orderdetail.ProductId = product.ProductId inner join Orders on Orders.Orderid = Orderdetail.orderid WHERE
Orders.orderdatum = @Orderdatum
END

When I execute this code the message I get:

Msg 201, Level 16, State 4, Procedure UpdateVoorraad, Line 0
Procedure or function 'UpdateVoorraad' expects parameter '@Orderdatum', which was not supplied.

(1 row(s) affected)

I want all the rows get affected.

Txs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 02:10:53

if you want to set it to getdate you need to make it optional first by giving a default value.
Also date logic has to be tweaked as below if you use GETDATE as it has time part also

see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html


ALTER PROCEDURE [dbo].[UpdateVoorraad]
@Orderdatum datetime = NULL

AS
BEGIN
set @Orderdatum = COALESCE(@Orderdatum,DATEADD(dd,DATEDIFF(dd,0,Getdate()),0))

UPDATE product SET
product.Voorraad = product.Voorraad - orderdetail.Tal
FROM
Product INNER JOIN OrderDetail ON
orderdetail.ProductId = product.ProductId inner join Orders on Orders.Orderid = Orderdetail.orderid 
WHERE
Orders.orderdatum >= @Orderdatum
AND Orders.orderdatum < @Orderdatum + 1
END

Once you do this, you can execute it like


EXEC [dbo].[UpdateVoorraad]

to run for current day

or


EXEC [dbo].[UpdateVoorraad] <Yourdatevaluehere>

to explicitly run for a given date value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources