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.

Whoops! Thanks. I was going to say I tried renamed the stops table as a diagnostic step, but that's clearly not the issue. — Aren Cambre
– Aren Cambre, Commented Mar 10, 2012 at 4:38

mu is too short · Accepted Answer · 2015-10-27 17:42:44Z

41

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"

edited Oct 27, 2015 at 17:42

answered Mar 10, 2012 at 4:18

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aren Cambre Over a year ago

You're right! They need to give out "bonehead" points here. I think I just earned one.

mu is too short Over a year ago

@ArenCambre: I think we'd all have an excess of "bonehead" points if there were such a thing :) Some of the hardest problems to see are the ones right in front of your face.

joragupra Over a year ago

Very nice answer. Just a small question: it should be FROM stops instead of FROM stop, right?

mu is too short Over a year ago

@joragupra Yes, it should have been (and now is) stops, thanks.

Abdellah Stands with Gaza · Accepted Answer · 2017-11-12 15:03:26Z

4

This can also happen if you mistype a table name. For example:

UPDATE profiles SET name = ( profile.first_name ) WHERE id = 1

Instead of profiles i incorrectly used profile !! This would work:

UPDATE profiles SET name = ( profiles.first_name ) WHERE id = 1

answered Nov 12, 2017 at 15:03

Abdellah Stands with Gaza

5,1781 gold badge30 silver badges36 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related