Update column result...

[tran008]

I need to update the resx column with the following:
Id num resx
151 686 NULL
152 686 3
153 686 X3
154 41 NULL
155 41 3
156 768 NULL
157 768 3
158 462 NULL
159 462 Y
160 642 NULL
161 642 3
162 098 NULL
163 098 3
164 537 NULL
165 537 Y
166 730 NULL
167 730 3


result:

Id num resx
151 686 3
152 686 X3
153 686 XX3
154 41 3
155 41 X3
156 768 3
157 768 X3
158 462 3
159 462 X3
160 642 Y
161 642 Y3
162 098 3
163 098 X3
164 537 Y
165 537 Y3
166 730 3
167 730 X3

I have looking into MAX and MIN function, but with no result. This is sql2000.

 

[monksnake]

I see no pattern to this at all.

<.

 

[bborissov]

DECLARE @Temp TABLE (NewId Int IDENTITY(1,1), RealId int)
INSERT INTO @Temp (RealId)
SELECT Id
       FROM YourTable
       ORDER BY Num, Id

UPDATE YourTable 
       SET ResX = REPLICATE('X',Tbl1.MaxId-Tbl1.MinId-1)+'3'
FROM YourTable
INNER JOIN (SELECT RealId, MIN(Id) AS MinId, MAX(Id) AS MaxId
                   FROM @Temp Tmp
                   GROUP BY RealId) Tbl1
ON YourTable.Id = Tbl1.RealId

not tested, make a good backup first and IO don't know HOW you define when you need 'X' and when you need 'Y'.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tran008]

Monksnake,

This is group by num column. If the resx col null then move the number from the second highest
to the col, and then update the second with 'X'+the value of the col and so on for the third, forth.
If the group mum start with 'Y' then add 3 to it, and add the col value + 3. eg:

Id num resx
151 686 NULL
152 686 3
153 686 X3
164 537 NULL
165 537 Y


Id num resx
151 686 3
152 686 X3
153 686 XX3
164 537 Y
165 537 Y3

 

[fuzzyocelot]

If I'm understanding the problem correctly, maybe this will help or at least give you an idea:

update table set resx = 3 where resx is null

update table set resx = 'X' + resx where len(resx)=1 or len(resx)=2

 

[fuzzyocelot]

Oops! Didn't see the other posts. Sorry!

 

[monksnake]

Look at num = 162 and num = 163 in your original post.
How do you know if that is an 'X' or 'Y'?


num = 160, shouldn't that be 3 instead of 'Y'?

<.