我想做的是在t2更新时更新t1。但是，我需要使用表t3来连接它们，这意味着我需要一个子查询。当满足一个条件时，我可以成功地更新t1;但是，当涉及多个条件时，我很难理解更新的语法--基本上我很难组合postgresql UPDATE、IF/ELSEIF/ELSE和一个子查询。

-- 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();