Update cloud table from SQL query?

Just ask support to run it

You can do your query using Advance Report and ensure the query runs correctly, after that create a scheduler and instead of using a join table to update the record , you can use php to check the values  for you before you  the update using sugar bean. 

Rodrigo, AFAIK that wouldn't work for Sugar Cloud

https://support.sugarcrm.com/Resources/Sugar_Cloud_Policy_Guide/#Database_Update_Requests
It says, literrally:
"SugarCloud customers do not have direct access to the database supporting their instance, and it is forbidden to install any utility which circumvents this restriction. Occasionally, there may be a need for direct database queries to be run against a cloud instance, and if this is needed, please open a support case. "

I believe your best / fastest option, since you tested this already, is as Dmytro Chupylka said: ask Support to run it for you.

Your other option would be to build a one-off custom Scheduler that does what you need using the beans of the modules in question, or using a executeQuery in code (I am assuming that is allowed, I have an OnSite Instance so I am not sure what Cloud limitations there are). Run your scheduler once, and then delete it, or disable it.

FrancescaS

Moreover, I would strongly recommend making updates via middle-layer as  Francesca Shiekhadvises - IMHO regularly bypassing the system's middle-layer with SQL updates will lead to the consequences in a form of inconsistent data in DB.

I also would like to admit that for modern CRM writing raw code to perform updates via middle-layer shouldn't be the only option.

Being not a developer but rather an admin to Sugar instance, I can be a superhero for my SugarCRM users  with low-code/no-code tool:

1.
Specify LB Service record in a click (technically -add an endpoint with configuring) - even without parameters




2. 
Design a logic hook with cloud  Logic Builder tool, it takes 7-10 minutes
(you may read the flowchart design easily by following the white line and hopefully recognize the logic equal to theTom's  SQL) 


3. 
generate zip in a click in Logic Builder interface and deploy zip into my Sugar via Module loader

4.
call the LB Service "runmyupdate1" once - directly from the Sugar interface

and benefit the results of update  - configured then performed:
- all in minutes
- without PHP knowledge
- via middle-layer, mitigating the risks of inappropriate update that lead to inconsistent data in Sugar Cloud DB