Set values in table based on minimum from groups in another table

Question

I'm trying to create a new table in pgAdmin using SQL, and since I'm a total novice I'm running into problems. My goal is to create a new table (called metadata) that summarizes values based on groupings from another table (called navigation).

Say the navigation table is like this:

VIDEO  LATITUDE
vid1   50
vid1   51
vid1   52
vid2   49
vid2   51

I need to create a metadata table that summarizes the navigation (with some other data); something like this:

VIDEO  minLATITUDE   LINK
vid1        50       http://foo
vid2        49       http://bar

I've been trying the SQL code (based on this question):

UPDATE f1188hw.YTmeta as a
SET minLat = b.lat
FROM f1188hw.YTmeta
INNER JOIN (SELECT video, min(lat) AS lat
    from f1188hw.nav
    group by video) AS b
ON a.video = b.video;

And pgAdmin returns:

ERROR: invalid reference to FROM-clause entry for table "a"
SQL state: 42P01
Hint: There is an entry for table "a", but it cannot be referenced from this part of the query.
Character: 158

I'm guessing I'm missing something obvious, but haven't been able to find it through searching and reading through pgAdmin documentation.

Vamsi Prabhala · Accepted Answer · 2017-01-26 00:17:39Z

1

Use

UPDATE f1188hw.YTmeta as a
SET a.minLat = b.lat
FROM (SELECT video, min(lat) AS lat
      from f1188hw.nav
      group by video) AS b
WHERE a.video = b.video;

Or

UPDATE f1188hw.YTmeta
SET minLat = (SELECT min(lat) 
              from f1188hw.nav
              where f1188hw.YTmeta.video = video 
             )

answered Jan 26, 2017 at 0:17

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

1 Comment

Evan Over a year ago

The latter suggestion works great and looks much more concise than my attempt.

Collectives™ on Stack Overflow

Set values in table based on minimum from groups in another table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related