CODEWITHSUNDEEP
sqlpostgresql
Evan
Evan

Reputation: 2038

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

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.

Upvotes: 0

Views: 31

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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 
             )

Upvotes: 1

Related Questions