Updating a column or deleting rows of a table in SQL transform (saving to the source table)
I have just started working in Domo for couple of weeks and currently working on the ETL transformation.
My requirement is to update or delete a column of a table using SQL transfrom and save the result to the same table. I have tried this using as individual transforms and in a Stored procedure, in vain. Help me in this.
Output is mandatory for SQL transform in DOMO and when i assigh the same name of the source to output, a new target table is created with the same name (while i want to impact the source table)
Note: I am surprised that DOMO created a duplicate table with same name as it can cause some conflits/misunderstanding later. I am curious to know its usecase, as I think creating another object with same name is not desirable.
Thank you for your time.
Thanks for sending that over.
I see what you are trying to do now. What you are doing is correct (Update and Delete). The issue you are running into is that the output data set is not the same as the input data set, even if you name the output data set the same as the input data set (behind the scenes they are different tables).
This is intentional on Domo's part for a few reasons:
1) We want to preserver and protect the source data sets
2) If you had the same output data set as the input data set then it would create a recursive data flow that would never stop running. This is because when the input data set is updated it kicks of the data flow.
Could you help me understand the use case better? If I know what you are trying to accomplish perhaps we can recommend another approach.0
Do you have any screen shots you can upload as an image in the post or as an attachment so we get a clearer understanding of what you're trying to accomplish?
I have attached the screenshot of what i am trying to do.
I am trying to update one row for a student_id and delete one row for a student id in the source table kvk_temo_studnet_target.
After i run that process, I get 2 datasets with the name kvk_temo_studnet_target while I would like to have one kvk_temo_studnet_target with the update and delete reflected.
Note: I updated your previous post as solution by mistake0
Could you also open each of the transforms and attached a screen shot of the code you are using? It will help me to understand what is happening and provide some guidance.
Apologies, I have included the screenshots of individual transforms as requested.
As they were simple queries i did not post them earlier, but i see your point.
To reiterate, I want to update/delete the source table within the same SQL dataflow task (if there is a way).
Please let me know if you need anything. Thank you.0
Thank you for the response. It helped.
My scenario was to have a history of data with a week of delta data appened (after removing the overlap in history data). I was able to acheive this with recursive data flow.
However, delete and update of a table in the dataflow was something I was not able to crack, and hence the question. Thanks for the help!0
I think that you are trying to accomplish an upsert. Update historical data with Delta data and add new rows if they exist, assuming that the historical table and delta table schemas are identical. This will give you a new output dataset containing the historical data upserted with the delta data.
You cas use the following 2 transforms in a RedShift dataflow:
DELETE FROM Historical_Data
USING Delta_Data WHERE Historical_Datal.id = Delta_Data.Id
*Uncheck "Generate Output Table"
insert into Historical_Data
select * from Delta_Data
*Uncheck "Generate Output Table"
Output Dataset :
*Dataset Name : UpSerted_Data
Select * Historical_Data
There are some changes that can be made to accommodate various sceneries.
Hope this helps.
**You can say "Thank you" by clicking the thumbs up in the post that helped you.1
- 7.7K All Categories
- 917 Connectors
- 242 Workbench
- 474 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 34 Visualize
- 198 Beast Mode
- 2K Charting
- 8 Variables
- 15 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 23 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部