Update cloud table from SQL query?

I need to update my revenue_line_items table to set the Manufacturer_ID, Category_ID and Type_ID fields with the corresponding values from the appropriate record in the product_templates table. I downloaded a backup instance from the cloud and imported into a local MySQL server to test the queries and they work fine. Unfortunately it appears that the syntax of the queries is not supported in the tools I have available to work with the cloud tables.

I have been using the Skyvia web service to access the cloud data, and have also tried the Advanced Queries in Sugar itself.

This is the query I am trying to run, modified for Category and Type.... I believe that the JOIN in the issue.

UPDATE revenue_line_items R
JOIN product_templates T ON R.product_template_id = T.id
SET R.manufacturer_id = T.manufacturer_id
WHERE R.manufacturer_id IS NULL

Any ideas would be appreciated. Different tool, revised query, etc.

Thanks

0 Dmytro Chupylka over 4 years ago

Just ask support to run it

Best Regards,
Dmytro Chupylka

integroscrm.com
We make work in Sugar CRM system faster, more convenient and efficient
0 Rodrigo Manara over 4 years ago

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 Manara

Sr. Developer
0 Dmytro Chupylka over 4 years ago in reply to Rodrigo Manara

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. "

Best Regards,
Dmytro Chupylka

integroscrm.com
We make work in Sugar CRM system faster, more convenient and efficient
0 Francesca Shiekh over 4 years ago

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
0 Dmytro Chupylka over 4 years ago in reply to Francesca Shiekh

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

Best Regards,
Dmytro Chupylka

integroscrm.com
We make work in Sugar CRM system faster, more convenient and efficient