sqlpostgresqlwith-clause

Postgres "missing FROM-clause entry" error on query with WITH clause


I am trying to use this query in Postgres 9.1.3:

WITH stops AS (
    SELECT citation_id,
           rank() OVER (ORDER BY offense_timestamp,
                     defendant_dl,
                     offense_street_number,
                     offense_street_name) AS stop
    FROM   consistent.master
    WHERE  citing_jurisdiction=1
)

UPDATE consistent.master
SET arrest_id = stops.stop
WHERE citing_jurisdiction=1
  AND stops.citation_id = consistent.master.citation_id;

I get this error:

ERROR:  missing FROM-clause entry for table "stops"
LINE 12: SET arrest_id = stops.stop
                         ^

********** Error **********

ERROR: missing FROM-clause entry for table "stops"
SQL state: 42P01
Character: 280

I'm really confused. The WITH clause appears correct per Postgres documentation. If I separately run the query in the WITH clause, I get correct results.


Solution

  • From the fine manual:

    There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the FROM clause.

    So you just need a FROM clause:

    WITH stops AS (
        -- ...
    )
    UPDATE consistent.master
    SET arrest_id = stops.stop
    FROM stops -- <----------------------------- You missed this
    WHERE citing_jurisdiction=1
      AND stops.citation_id = consistent.master.citation_id;
    

    The error message even says as much:

    ERROR: missing FROM-clause entry for table "stops"