sql loop update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql loop update

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-13 : 16:49:21

Hi i have 2 tables,

1. Member - Id, ClientId, Name, GroupId
2. Groups - Id, Name

the GroupId is the link.

i need a stored proc to do the following...

1. loop through the Groups table where Members.ClientId = 1
2. let say the first group is called "GP1"
3. if "GP1" has more than 400 Members
4. create a new group called "GP1 + Cont.." (GP1 Cont..)
5. and then update the Members.GroupId over 400 to the new "GP1A" id

then this will need to be recursive, so if "GP1" has 1000 Members, the 2 new groups will be created.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 17:00:59

Use ROW_NUMBER of you are using SQL Server 2005.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 23:03:14

you mean create new group in groups table? in that case you also need to update the same new groupids in member table using output clause

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-14 : 03:39:17

could i have an example to get me started

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 03:57:35

Well, are you using SQL Server 2005? Or SQL Server 2000?
Or other RDBMS?

E 12°55'05.63"
N 56°04'39.26"

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-14 : 08:54:35

sql server 2005

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-15 : 15:28:12

could someone please help me with this

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:16:00

useOUTPUT clause as suggested. something like


DECLARE @INSERTED_GROUPS TABLE
(
Group_name varchar(50)
)


INSERT Groups (Name)
OUTPUT INSERTED.Name INTO INSERTED_GROUPS 
SELECT t.Name + '_'+CAST(v.number AS varchar(5))
FROM
(SELECT g.Name,COUNT(*) AS RecCnt
FROM Groups g
INNER JOIN Member m
ON m.GroupId=g.Id
WHERE m.CleintId=1
GROUP BY g.Name
HAVING COUNT(*) >400
)t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number>0
AND v.number<=((t.RecCnt-1)/400)


UPDATE m
SET m.
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Id) AS Seq,m1.GroupId,g.Name 
FROM Member m1
JOIN Group g
ON g.Id=m1.GroupId) m
JOIN @INSERTED_GROUPS ig
ON LEFT(ig.GroupName,CHARINDEX('_',ig.GroupName)-1)=m.Name
AND SUBSTRING(ig.GroupName,CHARINDEX('_',ig.GroupName)+1,LEN(ig.GroupName))=((m.Seq-1)/400)

craigmacca
Posting Yak Master

142 Posts

Posted - 2009-01-18 : 04:36:11

Hi many thanks, just not sure what to do on line SET m. getting a Incorrect syntax near the keyword 'FROM'.

UPDATE m
SET m.
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Id) AS Seq,m1.GroupId,g.Name
FROM Member m1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-18 : 08:50:39

it should be


DECLARE @INSERTED_GROUPS TABLE
(
Group_Id int,
Group_name varchar(50)
)


INSERT Groups (Name)
OUTPUT INSERTED.Id,INSERTED.Name INTO INSERTED_GROUPS 
SELECT t.Name + '_'+CAST(v.number AS varchar(5))
FROM
(SELECT g.Name,COUNT(*) AS RecCnt
FROM Groups g
INNER JOIN Member m
ON m.GroupId=g.Id
WHERE m.CleintId=1
GROUP BY g.Name
HAVING COUNT(*) >400
)t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number>0
AND v.number<=((t.RecCnt-1)/400)


UPDATE m
SET m.GroupId=ig.Group_Id
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Id) AS Seq,m1.GroupId,g.Name 
FROM Member m1
JOIN Group g
ON g.Id=m1.GroupId) m
JOIN @INSERTED_GROUPS ig
ON LEFT(ig.GroupName,CHARINDEX('_',ig.GroupName)-1)=m.Name
AND SUBSTRING(ig.GroupName,CHARINDEX('_',ig.GroupName)+1,LEN(ig.GroupName))=((m.Seq-1)/400)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources