从另一个表更新表

Question

我有2个表“animal_breeds”和“ztmp.ztmp_509810_anims_out”。在“动物繁殖”中，每只动物都有关键和品种的名称和百分比。很少有动物可能有两种不同比例的不同品种。现在，基于动物的钥匙“animals_breeds”我想更新“ztmp.ztmp_509810_anims_out”

我使用这个代码，我知道错了

update ztmp.ztmp_509810_anims_out 
set 
    alt_id1 = ab.breed 
    ,alt_id2 = pcnt 
    ,alt_id3 = ab.breed 
    ,alt_id4 = pcnt 
    ,alt_id5 = ab.breed 
    ,alt_id6 = pcnt 
    ,alt_id7 = ab.breed 
    ,alt_id8 = pcnt 

from animal_breeds ab 
where ab.soc_code = ztmp_509810_anims_out.soc_code and ab.animals_key = ztmp_509810_anims_out.animals_key 
    and ab.soc_code = 'AUNDB'; 

我可以用一个用于在更新语句中循环？

Answer 1

UPDATE ztmp.ztmp_509810_anims_out AS z 
SET soc_code = q.soc_code, 
    animals_key = q.animals_key, 
    alt_id1 = breeds[1], 
    alt_id2 = pcnts[1], 
    alt_id3 = breeds[2], 
    alt_id4 = pcnts[2] 
FROM (SELECT soc_code, animals_key, 
      array_agg(breed) breeds, array_agg(pcnt) pcnts 
     FROM animal_breeds 
     GROUP BY soc_code, animals_key 
    ) q 
WHERE z.soc_code = q.soc_code 
    AND z.animals_key = q.animals_key; 

如果可以有2米以上的品种每animals_key，加breeds[3]和pcnts[3]等。