How to insert one column from a table into another based on a join/where clause

Question

I have two tables, temp_am and amphibian. The relationship between the two tables comes from the lake_id and the survey_date column in both tables. Both tables have 24,109 entries.

temp_am

id	lake_id	survey_date
1	10,001	7/25/2001
5	10,005	7/27/2001
6	10,006	7/29/2001

etc...

amphibain

id	lake_id	survey_date	amhibian_survey_id
1	10,002	7/25/2001
2	10,005	7/27/2001

etc...

I want to input the temp_am.id into the amphibian.amphibian_survey_id when both lake_ids and survey dates equal each other.

I have tried this sql query but it never worked. I canceled the query after 600 seconds as I figured a 29,000 observation table should not take that long. Please let me know if you see any issues in my query statement.

update amphibian 
set amphibian_survey_id = tm.id 
from amphibian a
inner join temp_am tm 
on a.lake_id = tm.lake_id 
and a.survey_date = tm.survey_date 

This query worked in microsoft access but not on DBeaver

UPDATE amphibian 
inner JOIN amphibian_survey_meta_data md ON 
(amphibian.survey_date = md.survey_date) AND (amphibian.lake_id = md.lake_id) SET amphibian.amphibian_survey_id = [md.id];
 

Answer 1

Postgres does not require repeating the table name for an update join. In this case even the join is not necessary just set <column> = ( select ... ) is sufficient. See demo here.

update amphibain a  
   set amhibian_survey_id = 
       ( select tm.id 
           from temp_am tm 
         where (tm.lake_id, tm.survey_date) = 
               (a.lake_id,  a.survey_date)
       ) ;