Redshift Guy Redshift Guy - 17 days ago 9
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


3 NULL
(Since it does not exist in processes table)



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