question

isha30 avatar image
isha30 asked

Urgent query - Update on columns based on another column

Hi, I have a table as abc .The initial state is: ![alt text][1] I want the output as below: ![alt text][2] Kindly let me know how to achieve the above updates. Thanks in advance ! [1]: /storage/temp/802-initial.jpg [2]: /storage/temp/803-result.jpg
updatecolumns
initial.jpg (27.5 KiB)
result.jpg (28.6 KiB)
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Can you explain the rules that determine what should appear in columns C and D?
0 Likes 0 ·
isha30 avatar image isha30 commented ·
C is for single * and D is for double *. So whenever I encounter a * in B, all rows<= to it should get updated by that value in C and so on. Similarly for D, wherever I get double * in B , the rows <= should get updated by that value in D.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
I'm having trouble seeing how the desired output matches the rules. Row 4 has *1 in column B. Should all rows in column C where ID > 4 get updated to *1 (ID = 4 being the first row where B = *1) or should all rows in column C where ID > 1 get updated to *1? I could see either rule being applied for B = *1, but both of my interpretations break down for C when B=*2
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@KenJ the rules are UPPER bounds, so it's all rows prior that need to be 'updated' - that's why row 10, column D is empty as there has been no **? in rows 10+. That's how I read it anyway :)
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Thanks! I even typed the inequality backward. This may be a long day.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Here's a quick example that shows the result you want, but I'm guessing there's more to the 'real' table than this, so it might not perform against your live data, but might get you thinking along the right lines. This query uses self joins to query against itself for the specific subsets of data declare @YourTable table ( ID int, B varchar(10), C varchar(10), D varchar(10) ) insert into @YourTable select 1,null,null,null insert into @YourTable select 2,null,null,null insert into @YourTable select 3,null,null,null insert into @YourTable select 4,'*1',null,null insert into @YourTable select 5,null,null,null insert into @YourTable select 6,'**1',null,null insert into @YourTable select 7,null,null,null insert into @YourTable select 8,null,null,null insert into @YourTable select 9,'**2',null,null insert into @YourTable select 10,'*2',null,null select * from @YourTable select t1.ID, t1.B, min(t2.B) as C, min(t3.B) as D from @YourTable t1 left join @YourTable t2 on t1.ID <= t2.ID and t2.B like '*[0-9]' left join @YourTable t3 on t1.ID <= t3.ID and t3.B like '**[0-9]' group by t1.ID, t1.B order by t1.ID and gives this result ID B C D ----------- ---------- ---------- ---------- 1 NULL *1 **1 2 NULL *1 **1 3 NULL *1 **1 4 *1 *1 **1 5 NULL *2 **1 6 **1 *2 **1 7 NULL *2 **2 8 NULL *2 **2 9 **2 *2 **2 10 *2 *2 NULL
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

isha30 avatar image isha30 commented ·
Thanks for the answer Kev :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.