The SET keyword has a variable name and a value.
You can generate the value using a correlated subquery, just make sure it returns only one observation.
Or are you trying to tell UPDATE which observations to change? For that you use a WHERE condition. Again if the test to identify which observations to update involves another dataset then use a subquery, perhaps using EXISTS ().
Perhaps something like this?
update AllDataSimple
set AllDataSimple.gasprice =
( select forward_gasprices.price
from AllDataSimple
inner join work.forward_gasprices
on forward_gasprices.DeliveryDate = AllDataSimple.DeliveryDate
and forward_gasprices.DeliveryDate > today()-1
)
where exists
( select forward_gasprices.price
from AllDataSimple
inner join work.forward_gasprices
on forward_gasprices.DeliveryDate = AllDataSimple.DeliveryDate
and forward_gasprices.DeliveryDate > today()-1
)
;
... View more