Forums - Thread Details
  • State Not Answered
  • Replies 5 replies
  • Subscribers 319 subscribers
  • Views 1566 views
  • Users 0 members are here
Forum Actions
Related

Update cloud table from SQL query?

Tom Repetti

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

Parents
  • 0

    I believe your best / fastest option, since you tested this already, is as 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

Reply
  • 0

    I believe your best / fastest option, since you tested this already, is as 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

Children
  • 0 in reply to Francesca Shiekh

    Moreover, I would strongly recommend making updates via middle-layer as  advises - 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