sql - 如何修复 'missing FROM-clause entry for table' ？

Question

我有一个查询功能。如果条件为真，我会用新值更新一行。

我做一个查询:

SELECT * FROM transfer_flight(41313, '2017-08-15 20:00:00+05');

我得到一个错误:

ERROR: missing FROM-clause entry for table "flights" LINE 1: SELECT (flights.scheduled_departure < $2) ^ QUERY: SELECT (flights.scheduled_departure < $2) CONTEXT: PL/pgSQL function transfer_flight(integer,timestamp with time >zone) line 7 at IF

CREATE OR REPLACE FUNCTION flightFunc(
    flight_identificator INTEGER, 
    new_timestamp timestamp with time zone) 
RETURNS TABLE(
    flight_id INTEGER,
    flight_no CHARACTER(6),
    departure_airport character(3),
    scheduled_departure timestamp with time zone,
    arrival_airport character(3),
    scheduled_arrival timestamp with time zone
)
LANGUAGE plpgsql
AS $$
DECLARE 
    flight_d timestamp with time zone =  scheduled_arrival - 
scheduled_departure;

BEGIN

    IF (flights.scheduled_departure < $2)
    THEN
        UPDATE flights
            scheduled_departure = $2,
            scheduled_arrival = $2 + flight_d
        FROM flights
        WHERE flights.flight_id = $1
        RETURNING 
            flights.flight_id,  
            flights.flight_no,
            flights.departure_airport,
            flights.scheduled_departure,
            flights.arrival_airport,        
            flights.scheduled_arrival;
    END IF;

END;
$$;

结果我只需要用输入的 flight_identificator 更新一行

Answer 1

删除 FROM 子句!这执行了一个CROSS JOIN。你需要 SET。所以:

UPDATE flights
    SET scheduled_departure = $2,
        scheduled_arrival = $2 + flight_d
    WHERE flights.flight_id = $1
RETURNING 
    flights.flight_id,  
    flights.flight_no,
    flights.departure_airport,
    flights.scheduled_departure,
    flights.arrival_airport,        
    flights.scheduled_arrival;