?
Solved

SQL Update only some records using a loop

Posted on 2007-11-26
10
Medium Priority
?
159 Views
Last Modified: 2010-04-21
I am having an issue trying to update a column using a loop. I have a variable that I pass into the stored procedure of qty, which is how many records should be updated. The update needs to only update however many rows that qty variable has in it. I have a table that holds parts that we need to order, our order process allows a user to see what is on hand at the wherehouse and then select as many items that are available to have shipped out to our location. So if the user sees that we have 100 items in the wherehouse they can submit an order for 50. What needs to happen is that the value of 50 is passed into the stored procedure and then updates the first 50 items with that part number so that we can flag them as being reserved.
I have tried writing a loop in my stored procedure to update as many records as the value has but it keeps updating every single record, thereby reseving all the parts. I know I must be missing something small here, as usual.
@price_id int
,@destination_id int
,@qty int
AS
DECLARE @counter int
	SET @counter = 0
	WHILE @counter < @qty
BEGIN
SET @counter = @counter + 1
INSERT INTO COT_FWD_XFER (price_id, destination_id, qty)
VALUES (@price_id, @destination_id, 1)
END
BEGIN
DECLARE @counter2 int
	SET @counter2 = 0
	WHILE @counter2 < @qty
 
SET @counter2 = @counter2 + 1
UPDATE COT_PO_Items SET qtyTransfer = 1 
FROM COT_PO_Items 
INNER JOIN COT_Items ON COT_PO_Items.cot_items_id = COT_Items.cot_items_id 
INNER JOIN COT_PO ON COT_PO_Items.cot_po_id = COT_PO.cot_po_id 
WHERE (COT_Items.price_id = @price_id) 
AND (COT_PO.destination_id = @destination_id) 
 
END

Open in new window

0
Comment
Question by:digital_slavery
  • 6
  • 4
10 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 20354215
Hi

How many rows returned by the select?

Cheers
  David
select *
-- UPDATE COT_PO_Items SET qtyTransfer = 1 
FROM COT_PO_Items 
INNER JOIN COT_Items 
    ON COT_PO_Items.cot_items_id = COT_Items.cot_items_id 
INNER JOIN COT_PO 
    ON COT_PO_Items.cot_po_id = COT_PO.cot_po_id 
WHERE 
    (COT_Items.price_id = @price_id) 
    AND (COT_PO.destination_id = @destination_id) 

Open in new window

0
 
LVL 1

Author Comment

by:digital_slavery
ID: 20354327
It is returning all records from both locations, it should only be returning 19 records from the destination_id I used as a parameter.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20354363
Hi,

Please post sample data and table structure that reproduces your problem.

Cheers
  David
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:digital_slavery
ID: 20354405
Ok, well here is the good news, I needed to exclude items that had a null value, so now it only updates the records with that available. But it is still ignoring my loop as to how many records that need to be updated and then stopping.
0
 
LVL 1

Author Comment

by:digital_slavery
ID: 20354420
Here is the query and like I said it ignors the loop and instead updates all the items with the price_id. So it is working, its just not updating only the amount of rows that the qty parameter is specifying.
DECLARE @counter2 int
	SET @counter2 = 0
	WHILE @counter2 < @qty
BEGIN
SET @counter2 = @counter2 + 1
UPDATE COT_PO_Items SET qtyTransfer = 1 
FROM COT_PO_Items 
INNER JOIN COT_Items ON COT_PO_Items.cot_items_id = COT_Items.cot_items_id 
INNER JOIN COT_PO ON COT_PO_Items.cot_po_id = COT_PO.cot_po_id 
WHERE (COT_Items.price_id = @price_id) 
AND (COT_PO.destination_id = @destination_id) 
AND COT_PO_Items.qtyReceived IS NOT NULL
AND EXISTS (SELECT     COT_PO_Items.qtyReceived, COT_PO.destination_id
FROM         COT_PO_Items INNER JOIN
                      COT_PO ON COT_PO_Items.cot_po_id = COT_PO.cot_po_id INNER JOIN
                      COT_Items ON COT_PO_Items.cot_items_id = COT_Items.cot_items_id
WHERE     (COT_PO.destination_id = 2) AND (COT_Items.price_id = 1129) AND (COT_PO_Items.qtyReceived IS NOT NULL))
END

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 20354452
Hi,

Try this query

I've used alias's int he inner query, to see if that makes a difference.

Cheers
  David
DECLARE @counter2 int
 
SET @counter2 = 0
WHILE @counter2 < @qty BEGIN
 
	SET @counter2 = @counter2 + 1
 
	UPDATE COT_PO_Items 
	SET qtyTransfer = 1
	FROM COT_PO_Items
	INNER JOIN COT_Items
		ON COT_PO_Items.cot_items_id = COT_Items.cot_items_id
	INNER JOIN COT_PO
		ON COT_PO_Items.cot_po_id = COT_PO.cot_po_id
	WHERE
		(COT_Items.price_id = @price_id)
		AND (COT_PO.destination_id = @destination_id)
		AND COT_PO_Items.qtyReceived IS NOT NULL
		AND EXISTS
			(
			SELECT
				pi_items.qtyReceived
				, i_po.destination_id
			FROM COT_PO_Items pi_items
			INNER JOIN COT_PO i_po
				ON pi_items.cot_po_id = i_po.cot_po_id
			INNER JOIN COT_Items i_items
				ON pi_items.cot_items_id = i_items.cot_items_id
			WHERE
				(i_po.destination_id = 2)
				AND (i_items.price_id = 1129)
				AND (pi_items.qtyReceived IS NOT NULL)
			)
 
END

Open in new window

0
 
LVL 1

Author Comment

by:digital_slavery
ID: 20354518
First thanks for your efforts here, it certainly is appreciated! The query does not update the qtyTransfer column at all now, it just remains at 0.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 750 total points
ID: 20354671
Hi,

>>
                  WHERE
                        (i_po.destination_id = 2)
<<

You had the destination hard coded at thsi point.

HTH
  David

PS Without the tables and sample data, I can't test anything!
0
 
LVL 1

Author Comment

by:digital_slavery
ID: 20355303
Well turns out that using a counter to update a set number of rows does not work! However I found the soulution to this problem which is to use ROWCOUNT. Everything is working just as it should. Thanks for your help and time and as such I will be awarding you points for your help.

Here is the link that I found that helped:
http://msdn2.microsoft.com/en-us/library/ms188774.aspx

Thanks again.
SET ROWCOUNT @qty;
UPDATE COT_PO_Items SET qtyTransfer = 1 
FROM COT_PO_Items 
INNER JOIN COT_Items ON COT_PO_Items.cot_items_id = COT_Items.cot_items_id 
INNER JOIN COT_PO ON COT_PO_Items.cot_po_id = COT_PO.cot_po_id 
WHERE (COT_Items.price_id = @price_id) 
AND (COT_PO.destination_id = @destination_id) 
AND COT_PO_Items.qtyReceived IS NOT NULL

Open in new window

0
 
LVL 1

Author Closing Comment

by:digital_slavery
ID: 31411101
I found the solution on my own
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question