Redshift Guy Redshift Guy - 4 months ago 30
SQL Question

Update table with join and if does not exists then NULL

I have a Master table with pid_name column varchar(40) and I want to update that pid_name colulmn with pname from another table processes.
Processes table has 2 column pid and pname with 100 distinct rows.

pid from master table has more than 50K distinct values for pid_name. I want to update pid_name with all values from processes table and NULL which does not exists in processes table.
I am using Redshift Database. Thanks in advance
Sample Data

Master Table before update

ID Pid_name

1 abc

2 def

3 ghi

processes table

pid pname

abc Process_1

def Process_2

Expected output in Master table after update

ID Pid_name

1 Process_1

2 Process_2

(Since it does not exist in processes table)

UPDATE master
SET pid_name =
    (SELECT pname
     FROM processes p
     WHERE = master.pid_name);