DML update scenario - 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

SQL Server 2000 Forums

SQL Server Development (2000)

DML update scenario

Author

Topic

44443
Starting Member

10 Posts

Posted - 2005-06-07 : 16:26:05

Group subid igrp id

001 1 NULL(1) 1
001 2 NULL(1) 2
001 4 NULL(1) 3
001 1 NULL(2) 4
001 2 NULL(2) 5
001 4 NULL(2) 6

Guys,

I have a scenario here which is depicted in the above sample data.
Each time '1' appears in subid column the corresponding igrp value should be updated with unique number
and the number should duplicated for the rest of igrp rows until the subid has 1 populated in it.
After coming across another 1 in subid, the igrp should be incremented by 1

For eg:

subid igrp

1 NULL(1)
2 NULL(1)
4 NULL(1)
1 NULL(2)
2 NULL(2)
4 NULL(2)

Any suggestions/inputs would be help indeed

Thanks

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 00:19:22

--- this will do that..


DECLARE @totalRecords INT
DECLARE @ctr INT, @key INT, @subID INT
SET @ctr = 1
--- get the last igrp ID
SELECT @key = COALESCE(MAX([igrp]),0) FROM YourTable
-- gets the total records
SELECT @totalRecords = MAX([ID]) FROM YourTable

WHILE @ctr <= @totalRecords
    BEGIN
            ---- Get the SubID
              SELECT @subID = subID 
                FROM YourTable 
             WHERE [ID] = @ctr    
           ---- Increment the Key if SubID = 1      
               IF @subID = 1 SET @key = @key + 1 

          ---- Updates the Table
            UPDATE YourTable SET igrp = @key 
            WHERE [ID] = @ctr                        
            
        --increment the counter
        SET @ctr = @ctr + 1
   END

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 00:21:42

OR
this will always starts with igrp = 1 unlike from above it will get the last ID and increment it again
I suggest you use this one:


DECLARE @totalRecords INT
DECLARE @ctr INT, @key INT, @subID INT
SET @ctr = 1
--- get the last igrp ID
 SELECT @key = 0 
-- gets the total records
SELECT @totalRecords = MAX([ID]) FROM YourTable

WHILE @ctr <= @totalRecords
    BEGIN
            ---- Get the SubID
              SELECT @subID = subID 
                FROM YourTable 
             WHERE [ID] = @ctr    
           ---- Increment the Key if SubID = 1      
               IF @subID = 1 SET @key = @key + 1 

          ---- Updates the Table
            UPDATE YourTable SET igrp = @key 
            WHERE [ID] = @ctr                        
            
        --increment the counter
        SET @ctr = @ctr + 1
   END

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

44443
Starting Member

10 Posts

Posted - 2005-06-08 : 10:00:21

raclede thanks for your input, it does work for me

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources