0

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

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 
0

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   |
+----+--------+---------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.