Updating Table After Left Join

Simple reprex below:

drop table if exists my_table;

create table my_table
(
    ref int,
    customer_name char(50)
);

insert into my_table(ref, customer_name)
values      (1001, 'Alan'), 
            (1002, 'Bob'),
            (1003, 'Christine'),
            (1004, 'David'),
            (1005, 'Elizabeth');

drop table if exists lookups;

create table lookups
(
    ref int,
    gender char(6)
);

insert into lookups(ref, gender)
values            (1001, 'male'),
                  (1002, 'male'),
                  (1004, 'male'),
                  (1005, 'female');

I'd like to update my_table to include gender, the values for which should be retrieved using a left join on the lookups table. I don't know how to do this.

Is it a two stage process ie do I need to first insert a gender column into my_table and then perform an update ... left join ... query?

Solution

Two step process. First, you need to add the gender column to my_table. Make sure it's nullable, because otherwise you'd have to declare it with a default, and assigning people a default gender seems... problematic.

Then just update my_table by joining to lookups. Inner join is fine here; you CAN do left join as well, but if you re-run this and use a left join, you risk updating any existing gender values with NULL where they don't exist in lookups

-- Create the column if it doesn't exist
if not exists 
(
    select 1
    from sys.columns
    where object_id = object_id('my_table')
        and name = 'gender'
)
begin
    alter table my_table
        add gender varchar(10) null
end
-- You need to do this in a separate batch or it won't know that 
-- the column was created and you'll get errors 
go
-- Update the genders.
update t
set gender = s.gender
from my_table t
inner join lookups s
    on t.ref = s.ref
-- not REALLY necessary, but in case you re-run this after the fact and something has changed
-- This will prevent that from getting wiped out
where t.gender is null