Add a new column from compare result

I need add a new column (status) on my main table (Table 1) on my bd Postgres, to do this , I need to compare two columns from table 1 on table 2 , if the two columns exist , I need to copy the status from table 2 to the new column on table 1 , if not exist , e need to fill the status column with ‘New’. Do youw know if it´s possible ? how ?

    TABLE 1 
HOST        | UUID
HOST - 1    | 3
HOST - 4    | 5
HOST - 3    | 6
HOST - 4    | 9
HOST - 5    | 10
HOST - 6    | 12
HOST - 8    | 2
HOST - 8    | 14

        TABLE 2     
HOST        | UUID  | STATUS
HOST - 4    | 9     | OPEN
HOST - 6    | 12    | ACCEPT
HOST - 8    | 14    | CLOSED

    TABLE 1 (new column status)
HOST        | UUID  | STATUS
HOST - 1    | 3     | NEW
HOST - 4    | 5     | NEW
HOST - 3    | 6     | NEW
HOST - 4    | 9     | OPEN
HOST - 5    | 10    | NEW
HOST - 6    | 12    | ACCEPT
HOST - 8    | 2     | NEW
HOST - 8    | 14    | CLOSED

Table 1

Table 2

Table 1 with a new status column

Thanks

Answer

You are describing a LEFT join of Table1 to Table2:

select t1.*,
       coalesce(t2.status, 'NEW') status
from Table1 t1 left join Table2 t2
on t2.uuid = t1.uuid and t2.host = t1.host

Advertisement

Answer