Multiple SQL Update Statements in Same Transform

Reply
Highlighted
Yellow Belt

Multiple SQL Update Statements in Same Transform

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"

Tags (4)

Accepted Solutions
Highlighted
Black Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post


All Replies
Highlighted
Black Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

Highlighted
Yellow Belt

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.

Highlighted
Black Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
Yellow Belt

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.

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.