Update Missing Data In Table

Question

I have a table with sample data set-up like below. How can I update the missing data for the field rglname? There will always only be ONE rglname assigned to region so I am not worried about duplicate values. Below is DDL

Create Table #BlueBell
(
  ID int
  ,region varchar(100)
 ,rglname varchar(500)
)

Insert Into #BlueBell VALUES
(1, 'East', 'Bob')
,(2, 'East', NULL)
,(3, 'East', NULL)
,(4, 'West', 'Sam')
,(5, 'West', NULL)
,(6, 'South', NULL)
,(7, 'South', NULL)
,(8, 'South', NULL)
,(9, 'South', 'Mitch')

And desired output is:

ID Region RGLName
1 East Bob
2 East Bob
3 East Bob
4 West Sam
5 West Sam
6 South Mitch
7 South Mitch
8 South Mitch
9 South Mitch

Answer 1

You can use the following update.

update #BlueBell set rglname = b.rglname
from #BlueBell a
inner join (
            select region, rglname
            from #BlueBell where rglname is not null
            ) b on a.region = b.region

Check your desired output here

select * 
from #BlueBell 

Answer 2

So you just want to copy the RGLName for a Region to the other rows with the same Region value that currently have a NULL RGLName?

If so, this will do it:

UPDATE 
    b
SET 
    b.rglname = bb.rglname
FROM 
    #BlueBell b 
JOIN
    #BlueBell bb 
on 
    b.region = bb.region
where 
    b.rglname IS NULL 
    and 
    bb.rglname IS NOT NULL

SELECT * FROM #BlueBells
+----+--------+---------+
| ID | region | rglname |
+----+--------+---------+
|  1 | East   | Bob     |
|  2 | East   | Bob     |
|  3 | East   | Bob     |
|  4 | West   | Sam     |
|  5 | West   | Sam     |
|  6 | South  | Mitch   |
|  7 | South  | Mitch   |
|  8 | South  | Mitch   |
|  9 | South  | Mitch   |
+----+--------+---------+