Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JohnDW
Starting Member

Belgium
45 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

3873 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
45 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
52326 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  
 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.11 seconds. Powered By: Snitz Forums 2000