Update Distinct Field Set In a Grouped Set

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)

Update Distinct Field Set In a Grouped Set

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-07-16 : 15:09:39

I am going to generlize this the best way I can because the real need is much more complex.

Say you have grouped sets as in you have 3 columns: 1) Group Number 2) Sequence Of Item In Group 3) Value

In each group there is for column 2 a value from 1 to say 10 and these can be duplicated in each group. What I want to do is start with group number 1 through # of groups and update the value of column 2. So that values in group 1 for sequences 1-# are 1-# and items in group 2 are values (#+1)-#.2 where # is the last number of the first group and #.2 is the last number of the second group after the value has been added to the last value of the first group.

I am sorry if that sounds confusing. Here is an example of data and how I would like it to be after the update. Value can be any value (in my case it would be a user id)

Before:
Group# Sequence Value
1 1 1
1 2 2
1 3 3
1 4 4
2 1 2
2 2 10
2 3 4
3 1 10
3 2 1
3 3 5
3 4 9
3 5 11

After:
Group# Sequence Value
1 1 1
1 2 2
1 3 3
1 4 4
2 5 2
2 6 10
2 7 4
3 8 10
3 9 1
3 10 5
3 11 9
3 12 11

Well I hope that is a good explanation ... a single update would be nice but not required.

Sort of like a double ranking update I think..

Edited by - onamuji on 07/16/2002 15:12:03

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-16 : 16:17:06

Warning!! untested code. I would have tried this out had you posted ddl and insert statements that I could have cut and paste

....


select
    group#,
    [Sequence]
    0 as newSequence
into
    #workingtable
from
    Onamuji
order by
    group# asc,
    [Sequence] asc

declare @newsequence int
select @newsquence = 0

update
    #workingtable
set
    @newsequence = newsequence = @newsequence + 1

update Onamuji
set [sequence] = wt.newSequence
from Onamuji o inner join #workingtable wt
        on o.group# = wt.group# and o.[Sequence] = wt.[Sequence]

drop table #workingtable

<O>

Edited by - Page47 on 07/16/2002 16:18:40

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-07-16 : 16:22:47

there is no DDL :) well there is but it's this huge decision table plus the result table that the decisions get placed into. I needed to process the decision table in the sets and then move the steps based on the decision set ... nice company business logic... all so the CEO's don't get mad that they didn't approve something before someone else did .. hee hee... thanks for you reply though :)

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-07-16 : 16:41:57

Well $%$@ I forgot one little detail that may change that query... The Sequence from each set can have multiple values so a distinct value of GroupType, Sequence, Value ... that means for GroupType 1 there can be Sequence = 1 and Value = 1 and Sequence = 2 and Value = 2 .... sorry for not mentioning this before .... any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-16 : 16:45:26

quote:
Well $%$@ I forgot one little detail that may change that query...

When I saw this:

quote:
I am going to generlize this the best way I can because the real need is much more complex

I just knew it was a trap...

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-07-16 : 21:49:07

lol i should have known better ... i've thought (actually typed up) this question several times before but each time it was like 15000 characters and didn't feel like posting a novel ...

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-07-17 : 08:48:52

Just to let you know that one query still worked the way I wanted with minor modifications ...

SET @Step = 0
SET @LastStep = 0
SET @LastSet = 0

UPDATE @Approvals
SET ActualStep = CASE WHEN @LastStep <> Step THEN @Step + 1 ELSE @Step END, @Step = CASE WHEN @LastStep <> Step THEN @Step + 1 ELSE @Step END, @LastStep = CASE WHEN @LastSet <> LogicalSet THEN 0 ELSE Step END, @LastSet = LogicalSet

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-07-17 : 09:18:10

And I am just posting this in case anyone else searches for something like this or i need this again :)

WHILE EXISTS(SELECT * FROM @Approvals WHERE ActualStep = 0)
BEGIN
UPDATE @Approvals
SET ActualStep = a.s + m.a
FROM (SELECT MIN(Step) AS s, MIN(LogicalSet) AS ls FROM @Approvals WHERE ActualStep = 0 AND Step = (SELECT MIN(Step) FROM @APprovals WHERE ActualStep = 0)) AS a,
(SELECT MAX(ActualStep) AS a FROM @Approvals) AS m
WHERE LogicalSet = a.ls
AND Step = a.s
AND ActualStep = 0
END

nifty little loop... wonder if there is a way to take it out of the while...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources