How can I change sync table data with SQL?
If you need to change sync table data via a PostgreSQL query, it’s not recommended to leave the query applied in the sync table’s SQL panel. This is because sync tables are completely overwritten at each sync, so it is not guaranteed that the query would automatically apply. Instead you can change the synced data with a trigger function.
The recommended workflow.
Create a sync table via the CARTO interface or the Import API, setting it to sync at a certain interval.
Save a stored procedure in your CARTO account that contains the queries you need to run on the dataset after it syncs.
Use an external component to schedule a task that calls your stored procedure. This external component can be a cron task on a client server, an AWS Lambda function, or any other service that allows you to run a simple script. There is no way to determine the exact timestamp at which CARTO will run the sync process, so we recommend that the task interval be half or a third of the sync interval. For example, if a sync table updates every x hours, the stored procedure should be called every x/2 or x/3 hours.