Multiple SQL Update Statements in Same Transform

user019875
user019875 Member
edited March 2023 in SQL DataFlows

I'm currently adding one Update Statement per one Transform process box/dataflow in the Domo MySQL tool. How would I combine them in the same process box/dataflow in Domo? Thank you.

 

/* Update Value1*/
UPDATE `table1`
SET `Title` = "My Updated Value1"
WHERE `Title` = "My Original Value1"

 

/* Update Value2 */
UPDATE `table1`
SET `Title` = "My Updated Value2"
WHERE `Title` = "My Original Value2"

Tagged:

Best Answer

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    Good news!

     

    This is a standard MySQL implementation question!

    https://www.mysqltutorial.org/mysql-update-join/

     

    You would do use an UPDATE with a JOIN to a table for JOINs based on a SELECT.

    Alternatively, given that it looks like in your example you want to update one column based on different criteria, you could accomplish that using a UPDATE with a CASE statement.

    https://www.w3schools.com/sql/func_mysql_case.asp

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user019875
    user019875 Member

    Thank you, @jaeW_at_Onyx. I had seen the second example in Stack Overflow, but I had never used that format before. When I used an Oracle database in a previous job, the ETL folks would tell me to write out each update statement line by line, which doesn't work in Domo. Thanks again.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    To be clear.  you CAN run each update statement line by line.  It just has to go into a different transform.

    Each SQL transform can only execute one SQL  statement.

     

    To get around that, some developers will implement stored procedures to execute a batch of commands.

    In that case you have one CREATE PROCEDURE and then one CALL

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user019875
    user019875 Member

    I have been doing one SQL UPDATE statement in separate SQL Transforms by the hundreds, but I have several hundreds more to add. That's why I'm looking for a more efficienct way of doing this. I'm not a developer but am acting like one in a small company environment. In the corproate world in prior experiences, my role was to just execute query statements for segmenting and extracting data, so this is good experience for me! Building datatsets nice and clean vs having 3 sets of developers building out tables that don't have consistent naming conventions and definitions across platforms and tables. Thanks for your help.

Categories