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
 General SQL Server Forums
 New to SQL Server Programming
 Update A table with a loop through a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JohnDW
Starting Member

Belgium
25 Posts

Posted - 09/20/2013 :  09:49:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 09/20/2013 :  10:00:41  Show Profile  Reply with Quote
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;
Go to Top of Page

JohnDW
Starting Member

Belgium
25 Posts

Posted - 09/20/2013 :  17:33:01  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/22/2013 :  02:10:53  Show Profile  Reply with Quote
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
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.09 seconds. Powered By: Snitz Forums 2000