Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Update only some records using a loop

Posted on 2007-11-26
10
Medium Priority
?
157 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

609 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