SQL Update Statement for ~20,000 items - LxWxH

[JeffOrtho]

I use Microsoft Dynamics GP 10. I need to update all dimensions of all items.
I know SQL and have googled around to see how to do a very large update on 3 columns for 20000+ rows but can't find anything that really looks like the best way possible without locking up my server.

How would I be able to do a very large update to a table. Would this be good enough? Even for 20000+ rows?
UPDATE IV00101
SET (IV00101.Length = NEW13.Length, IV00101.Width= NEW13.Width, IV00101.Height= NEW13.Height)
WHERE (IV00101.ItemNmbr = NEW13.ItemNmbr)

Thanks everyone!!
Jeff
Systems Admin

 

[SQLBill]

That will do the update, but without knowing the table structure it's difficult to give you a better method. I will suggest that you look at your data and table structure to see if you can loop through the rows in batches. For example: if you have a Primary Key or other column that numbers the rows, you could do: (somecol would be replaced with the column name that is being used to control the batches)

DECLARE @cntr INT
DECLARE @nextcntr INT
DECLARE @maxcntr INT

SET @cntr = 0
SET @nextcntr = 1000
SET @maxcntr = (SELECT COUNT(somecol) + 1 FROM IV00101)

WHILE @cntr < @maxcntr
 BEGIN
   UPDATE IV00101
   SET (IV00101.Length = NEW13.Length, IV00101.Width= NEW13.Width, IV00101.Height= NEW13.Height)
   WHERE (IV00101.ItemNmbr = NEW13.ItemNmbr)
     AND (IV00101.somecol > @cntr
      AND IV00101.somecol < @nextcntr)

   SET @cntr = @nextcntr + 1
   SET @nextcntr = @nextcntr + 1000
 END

You will have to test that....but it should work for you. I would highly advise making a backup of the current table/data what's up. An easy way to make a temporary backup is to run somehting like this:

SELECT * INTO Temp_IV00101
FROM IV00101

That makes it easy (somewhat) to rollback if the update goes wrong.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

You should also remove the parenthesis from the original code. It will likely cause problems.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JeffOrtho]

I have a test database to fool around in for things. So no worry there.
The information in the NEW table is just the item number, length, width, height, so nothing else would match as another column although i guess i could create a column with 1,2,3,4 as the value to separate this into batches. Never even thought about that.

Thanks! i'm going to just fool around testing large updates. i'll actually do the update tomorrow. is there a site that gets into a little more detail about SQL statements other than just the basics; say like this case here?

 

[SQLBill]

I use the Microsoft SQL Server Books On Line (BOL) that comes with SQL Server Client Tools and I make a lot of use of this forum.

In your table, is Item Number unique for each row? If so, you probably could use that.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[JeffOrtho]

Yes item number is unique. there is only one item number with its own information.