Solved

SQL Update only some records using a loop

Posted on 2007-11-26
10
148 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 250 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore 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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now