Home » SQL & PL/SQL » SQL & PL/SQL » sql update in a loop (SQL server 2005)
icon14.gif  sql update in a loop [message #336851] Tue, 29 July 2008 04:01 Go to next message
papapier
Messages: 2
Registered: July 2008
Junior Member
Hey guys,

is it possible to put this is in a loop, so every 3K users the organization number will rise by one?

UPDATE users
SET organization = 1
WHERE Fname like '%LRuser100%'

note: valid Fname is 6 digits after the letters.(LRuser100000)

thanks for your attention and assistant.
Re: sql update in a loop [message #336855 is a reply to message #336851] Tue, 29 July 2008 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please precise your question and environment.
For instance, post a test case along with the result you want for this (your can replace 3K by 10 for a small example).

Regards
Michel
Re: sql update in a loop [message #336888 is a reply to message #336851] Tue, 29 July 2008 05:17 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
papapier
sql update in a loop (SQL server 2005)

You might find yourself surprised, but - this is Oracle Forum, not MS SQL Server one. Although certain solutions may be used in both databases, people here tend to post Oracle-specific ones. Therefore, perhaps you'd rather search for a more appropriate place to search answers to your questions of the same (MS SQL Server) kind.
Re: sql update in a loop [message #336916 is a reply to message #336855] Tue, 29 July 2008 06:12 Go to previous messageGo to next message
papapier
Messages: 2
Registered: July 2008
Junior Member

Hi,

my environment is Win2003 + SQL server 2005 sp2
i have 100000 users names LRuser100000-LRuser199999
the task is to update organization field per groups of 3000 users.
LRuser100000-102999 => Org '1'
LRuser103000-105999 => Org '2'
LRuser106000-108999 => Org '3'
LRuser109000-111999 => Org '4'
and so on for all users with this prefix.

i wrote this (my questions at bottom:

DECLARE @CounterUsr int, @CounterOrg int

SET @CounterUsr = 100
SET @CounterOrg = 1

WHILE @CounterUsr < 110
BEGIN
UPDATE Users
SET Organization = @CounterOrg
WHERE FName LIKE '%LRUser%' + CONVERT(varchar(3), @CounterUsr)

SET @CounterUsr = @CounterUsr + 1
IF ((@CounterUsr % 3) = 0) SET @CounterOrg = @CounterOrg + 1
END

1) i get error in running this: "The Declare cursor SQL construct or statement is not supported."
2) i see the IF doesn't work as expected

THANKS!
Re: sql update in a loop [message #336918 is a reply to message #336916] Tue, 29 July 2008 06:17 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Once again: this is NOT MS SQL Server forum; we deal with Oracle here.
Previous Topic: Recursive Query
Next Topic: number format
Goto Forum:
  


Current Time: Tue Dec 06 06:34:41 CST 2016

Total time taken to generate the page: 0.07916 seconds