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.
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"