Carto Builder update static dataset with fields from synced, real-time dataset

Question

I have two datasets in Carto that I am trying to join. One is static of administrative areas (counties), and the other contains indicator scores calculated on a separate server. My API outputs the data in CSV format, and I have it added to Carto and synced every hour.

I joined the two tables using an UPDATE query, but I don't see any indication that the synced data will update in the static table when the real-time data syncs. Furthermore, I'm having a hard time testing it, as the scores will change incrementally over days/weeks, not quickly.

Does anyone know if a JOIN like I've done will ensure the administrative table updates when the synced table updates?

EDIT:

Sorry, I mistakenly put 'Editor' before. I'm using Carto Builder, the new UI.

Answer 1

An UPDATE query will not work in any case (Editor or BUILDER). This update would be performed a join just in the particular moment you apply the query.

If you are using BUILDER, these are the steps to join a dataset/layer with a synced table:

1. Create a sync table via connecting DATASET in CARTO.
2. Create a map selecting the counties dataset.
3. Add Join Columns from second layer to the counties layer.
4. Set the type of join, geometry and fields you want to get from both layers. Apply.
5. Publish or update your map.

The dataset would synced, and every time you open or refresh the map the join will run again with the updated data.