Solved

SQL Update only some records using a loop

Posted on 2007-11-26
10
149 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adventure works database .msi 4 60
index  - last use and update 8 56
format nvarchar field as mm/dd/yyyy 4 61
Query - which index being used? 2 47
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

919 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

16 Experts available now in Live!

Get 1:1 Help Now