SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE States
    ([State] varchar(7), [ABR] varchar(2))
;
    
INSERT INTO States
    ([State], [ABR])
VALUES
    ('Alabama', 'AL'),
    ('Alaska', 'AK'),
    ('Arizona', 'AZ')
;


CREATE TABLE Users
    ([User] varchar(7), [State_short] varchar(2), [State] varchar(10))
;
    
INSERT INTO Users
    ([User], [State_short], [State])
VALUES
    ('John', 'AL', NULL),
    ('Carl', 'AK', NULL),
    ('Ivan', 'AZ', NULL),
    ('Martin', 'AZ', NULL),
    ('William', 'AK', NULL),
    ('Sean', 'AL', NULL),
    ('Bob', 'AL', NULL)
;
10 rows affected

update Users
set State = (
    select s.State from States s where s.ABR = Users.State_short
)
where State is null
7 rows affected

SELECT * FROM Users


User
State_short
State


John
AL
Alabama

Carl
AK
Alaska

Ivan
AZ
Arizona

Martin
AZ
Arizona

William
AK
Alaska

Sean
AL
Alabama

Bob
AL
Alabama


update users set state = null;
7 rows affected

with cte as (
    select u.State, s.State New_state
    from Users u
    inner join States s on s.ABR = u.State_short
)
update cte set State = New_state
7 rows affected

SELECT * FROM Users


User
State_short
State


John
AL
Alabama

Carl
AK
Alaska

Ivan
AZ
Arizona

Martin
AZ
Arizona

William
AK
Alaska

Sean
AL
Alabama

Bob
AL
Alabama

User	State_short	State
John	AL	Alabama
Carl	AK	Alaska
Ivan	AZ	Arizona
Martin	AZ	Arizona
William	AK	Alaska
Sean	AL	Alabama
Bob	AL	Alabama