SQL UPDATE TABLE from SELECT query from other tables

Question

I have bd hf3 and 5 tables there: active_preset with columns (id , preset_id) preset with columns (id , birja_id, trend_id, fractal, interval_up) birja with columns (id , name) trend with columns (id , name) uq_active_preset with columns (id , birja, trend, fractal, interval_up)

In table preset I have a few records. Some of them are in table active_preset by foreign key preset_id. In table active_preset a few records exist once , a few more than once.

I need to update table uq_active_preset with records from table active_preset disregarding repetitions of records if they are present.

I did query from active_preset and it works good:

SELECT 
    b.name AS birja, p.fractal AS fractal , tre.name AS trend, p.interval_up AS interval_up  
FROM hf3.active_preset AS ap
INNER JOIN hf3.preset AS p on p.id = ap.preset_id
INNER JOIN hf3.birja AS b on b.id = p.birja_id
INNER JOIN hf3.trend AS tre on tre.id = p.trend_id
GROUP BY b.name, p.fractal, tre.name, p.interval_up
HAVING COUNT(*) >= 1

But I don't know how to update uq_active_preset

I tried this and it returns syntax error:1064 :

UPDATE hf3.uq_active_preset uap SET 
  uap.birja = st.birja ,
  uap.fractal = st.fractal,
  uap.trend = st.trend,
  uap.interval_up = st.interval_up,
 FROM (SELECT b.name AS birja, p.fractal AS fractal , tre.name AS trend, p.interval_up AS interval_up  
        from hf3.active_preset AS ap
        INNER JOIN hf3.preset AS p on p.id = ap.preset_id
        INNER JOIN hf3.birja AS b on b.id = p.birja_id
        INNER JOIN hf3.trend AS tre on tre.id = p.trend_id
        GROUP BY b.name, p.fractal, tre.name, p.interval_up
        HAVING COUNT(*) >= 1
        ) st

Answer 1

when you make an update using from is like you join the updated table with your query result. So, you need also a where statement in order to tell where those two are connected. Also, don't use alias of your updated table on set statement. You need something like that:

UPDATE hf3.uq_active_preset uap SET birja=st.birja,fractal=st.fractal,trend=st.trend,interval_up=st.interval_up
 FROM (SELECT b.name AS birja, p.fractal AS fractal , tre.name AS trend, p.interval_up AS interval_up  
        from hf3.active_preset AS ap
        INNER JOIN hf3.preset AS p on p.id = ap.preset_id
        INNER JOIN hf3.birja AS b on b.id = p.birja_id
        INNER JOIN hf3.trend AS tre on tre.id = p.trend_id
        GROUP BY b.name, p.fractal, tre.name, p.interval_up
        HAVING COUNT(*) >= 1
        ) st
where uap.fkey=st.fkey