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

Question

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.

Answer 1

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"