sqlsql-server

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