PostgreSQL UPDATE trigger on SELECT using IF/ELSE statements

Question

What I'd like to do is update t1 whenever t2 is updated. However, I need to use table t3 to join them, meaning I need a subquery. I can successfully update t1 when one condition is met; however, I'm having trouble understanding the syntax for updating when multiple conditions are involved - essentially I'm having trouble combining postgresql UPDATE, IF/ELSEIF/ELSE, and a subquery. I'm looking for something along these lines:

-- TABLES

create table t1
(
serial_number        integer primary key,
current_location     varchar
);

create table t2
(
some_id              bigserial primary key,
status               integer
);

create table t3
(
serial_number        integer REFERENCES t1(serial_number),
some_id              integer REFERENCES t2(some_id),
unique(serial_number, some_id)
);


-- TRIGGER

create or replace function trigger_update_currentlocation()
returns trigger as 
$body$
begin

-- If SUBQUERY status is deployed (0), retrieved (1), or lost (2), then update t1 with appropriate current_location. 

-- THIS IS WHAT FAILS - SQL does not recognize 'SUBQUERY.status' in each if/elseif statement.
update t1
    if SUBQUERY.status = 0 then
        set t1.current_location = 'Deployed'

    elseif SUBQUERY.status = 1 then
        set t1.current_location = 'Retrieved'

    elseif SUBQUERY.status = 2 then
        set t1.current_location = 'Lost'

-- This joins t3 and t2 in order to select only serial_numbers with a status. Column `some_id` does not exist in t1 and thus can't be used to join t1 to t3 directly. 
from (
    select t3.serial_number, t2.status
    from t2 inner join t3
        on t2.some_id = t3.some_id
    ) as SUBQUERY

-- This matches SUBQUERY serial number to t1 serial number, so that only the appropriate rows in t1 are updated.
where SUBQUERY.serial_number = t1.serial_number;

end;
$body$
language plpgsql;

CREATE TRIGGER "deployLocations" AFTER update ON t2 FOR EACH ROW EXECUTE 
PROCEDURE trigger_update_currentlocation();

Answer 1

This query should do what you need:

update t1 
  set current_location = 
    case t2.status
        when 0 then 'Deployed'
        when 1 then 'Retrieved'
        when 2 then 'Lost'
    end,
    date_updated = now()
from t2 inner join t3
   on t2.some_id = t3.some_id
where t3.serial_number = t1.serial_number;

Answer 2

Replace if else with case statement in update, it will take care of the problem you are running into.

update t1
        set t1.current_location =   CASE  
                                    WHEN SUBQUERY.status = 0 THEN 'Deployed' 
                                    WHEN SUBQUERY.status = 1 THEN 'Retrieved' 
                                    WHEN SUBQUERY.status = 2 THEN 'Lost' 
                                    ELSE t1.current_location
                                END 
from (
    select t3.serial_number, t2.status
    from t2 inner join t3
        on t2.some_id = t3.some_id
    ) as SUBQUERY

where SUBQUERY.serial_number = t1.serial_number;