Update column with top 1 value in the group

Transact-SQL
1

Untitled.png1366×768 58.9 KB
how to update column with group wise. here am pasted my raw file .
treat same account no members as one group.
i want 3 groups with same area
1.i want update are column based on accountno wise and if area is null group wise.
2 . if in the group 1 record is empty it will update with top not null value in same group
3. if group having different area values it should update with top one are value.

i tried this is code . but its updateing total groups with same value.

Update A
Set a.area= (select top 1 Area from TABLE as A where A.area is not null and accountno=accountno)

SNO Name accountNo Area
1 sateesh 123 rcvsh
2 gowtham 123
3 vinod 123
4 krishna 123
5 gd 123
6 Anil 124
7 swetha 124
8 tanuj 124 family
9 lakshmi 126 friend
10 rakesh 126 collegue
11 santosh 126 manger
12 viswa 126 relative

2

Your update statement is missing a WHERE clause (not the subquery)

Therefore there is no constraint on what rows get udpated, so they all do.

1 Like
3

Hi Gbritton,
thanks for the replay.
let me know the below code is right or wrong.

Update A
Set a.area= (select top 1 Area from TABLE as A where A.area is not null and accountno=accountno) where area is null

4

should do it

5

There are syntax error in this code. Are you sure it does execute ?