从两个独立的主键ID填充外键ID- postgres

Question

我有两个表，penn_survey和panama_survey，它们连接到VES表，两者都是一个太多的关系。下面的查询将将“multi_survey_id”列f.key填充到两个调查表中。查询将正确填充一个表。但是，当我运行第二个查询连接第二个调查表并再次填充第二个调查表的'multi_survey_id‘时，它会从'multi_survey_id’列的第一个调查表中删除ID。

我如何保存第一组‘多调查_ids’？

-- VES: add unique ID, p.key, join id on site/date/survey_time/detection_type, and create f.key     
alter table ves 
add column ves_id UUID default (public.uuid_generate_v4());

alter table ves 
add primary key(ves_id);

alter table ves 
add column mult_survey_id UUID;

alter table ves 
alter column "date" type date using ("date"::text::date);

--------- VES to panama_survey
update ves v 
set mult_survey_id = 
    (select ps.panama_survey_id
    from panama_survey ps
    where (ps.site, ps."date", ps.survey_time, ps.detection_type) = (v.site, v."date", v.survey_time, v.detection_type));
    
--------- VES to penn_survey
update ves v 
set mult_survey_id =
    (select pes.penn_survey_id
    from penn_survey pes
    where (pes.site, pes."date", pes.survey_time, pes.detection_type) = (v.site, v."date", v.survey_time, v.detection_type));

Answer 1

通过将"is null“合并到第二次更新中，解决了这个问题。

--------- VES to panama_survey
update ves v 
set mult_survey_id = 
    (select ps.panama_survey_id
    from panama_survey ps
    where (ps.site, ps."date", ps.survey_time, ps.detection_type) = (v.site, v."date", v.survey_time, v.detection_type));
    
    
--------- VES to penn_survey
update ves v 
set  mult_survey_id =
    (select pes.penn_survey_id
    from penn_survey pes
    where (pes.site, pes."date", pes.survey_time, pes.detection_type) = (v.site, v."date", v.survey_time, v.detection_type))
where mult_survey_id is null;

通过这个，我发现您不能在同一列中有多个f.keys。因此，现在，我已经从每个唯一的调查表中为f.keys创建了两个单独的列。