CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Rahul Gupta
Rahul Gupta

Reputation: 1802

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)

Upvotes: 1

Views: 358

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246103

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

Upvotes: 4

Related Questions