?
Solved

SQL Update only some records using a loop

Posted on 2007-11-26
10
Medium Priority
?
156 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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

Technology Partners: 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!

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

777 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