SQL Server Performance Forum – Threads Archive

Help needed with this problem

I have a Table A Field1
Field2
Field3
Field4 Now Field1 and Field2 are not unique but Field3 and Field4 are unique so for every Field1 there are many Field2 and for every Field2 there are unique many Field3 and for every Field3 there is only one Field4 eg
1 / 2 / 8 / 9
1 / 2 / 7 / 0
1 / 2 / 6 / 0
1 / 3 / 5 / 7 Note /=Field seperator
Now if you see that 1 has Three 2’s and for every 2 there is corresponding 8,7,6
Now my problem is that for 6,7,8 they should have only 9 and Field4 ie when 7 and 6 dont have field 4 ie =0 then they should be updated by 9. How to do that ?
Thanks in advance

---

Try this Update T1
set col4=T2.col3
from yourTable T1 inner join
(Select col1,col2,max(col4)as col3 from yourTable group by col1,col2) T2
on t1.col1=t2.col1 and t1.col2=t2.col2
Madhivanan Failing to plan is Planning to fail

---

Thanks Madhivanan
that worked perfect.Great help and saved lot of my time. With regards

---

]]>