Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to: Update 2 columns in a SQL table using the Write Data In -DB tool

Purvy85
6 - Meteoroid

I have built a workflow that I will use to load a Type 2, slowly changing dimensional SQL table.

 

The only point I am now stuck on is expiring the records in the database where an active row is to be created.

 

Basically Im trying to replace: 

*Row_Expired_Dt  -  todate(DateTimeAdd(DateTimeNow(),-1,"days"))

*Current_Row_Flag - 'N'

 

For all the rows that contain the UniqueID code.

 

In SQL I would use:

 

Update DIM_WORK_BREAKDOWN_STRUCTURE                                                                    --the table where the records need to be amended
SET [Row_Expired_Dt] = dateadd(day,-1, cast(getdate() as date)), Current_Row_Flag = 'N'     -- The columns where the values need to be updated

Where WBS_ID in (2673 ,2674 ,2675 ,20641 ,20943 ,14 ,15 ,16 ,17 ,18 ,19 ,20 ,21 ,20328)     -- The uniqueID for the record

This should be straight forward, however, I have noticed previous discussions have not really provided a solution.

 

The current process I have developed has the DataStream in containing the 14 Records that need to be expired loading into a Temp table so the join can take place. 
Joining on the UniqueID to bring out all the DB records that align

Where Im stuck!!!!

Configuring the Write Data In -DB tool to update the 2 columns where the ID matches

Jason_Purvis_0-1584071055862.png

 

I have tried many different methods, The incoming data has the original values with the updated already made and if this could update the DB directly that would be the optimum solution.

 

The records cannot be deleted and recreated as the UniqueID will change causing issues when the Fact Table joins the record...

 

All connections to the database are using ODBC and have confirmed DB admin rights over the table

Any help would be very greatly appreciated... This is the last piece of the puzzle 

 

Thanks in advance

 

5 REPLIES 5
TomWelgemoed
12 - Quasar

Hi @Purvy85 

 

I would, if volumes permit, stream the data out to a standard Alteryx Output. When you choose that option, you have the option to enter POST SQL after inserting the data into your temp table.

 

What I used to do is to write to a temp table and then to execute post sql afterwards to do the type 2 update on the target table. Then you can just use the SQL you're used to.

 

Hope that helps.

 

Regards,

Tom

Purvy85
6 - Meteoroid

Hi Tom,

 

Thanks very much for your response.

 

This may sound like a silly question. As I'm updating the columns with the static values of 'N' and 'Previous date' for the row expiry, the static part of the query is ok, however, I cant think how to add the required ID's that will be updated to the update query so I can schedule the workflows.

 

I hope that makes sense.

 

Many Thanks,

 

Jason

TomWelgemoed
12 - Quasar

Hi @Purvy85 (Jason),

 

I'm not 100% sure if I understood you correctly, but, simply put, I believe you're asking how to do the update query so you're updating the correct rows in the target table? If not, please correct me.

 

In short, this was the process I typically followed:

 

1. In the temporary table I'm creating, ensure the presence of unique identifiers that match the target table. For example, a customer ID or transaction ID etc. If that doesn't usually come with your raw data, you may need to join with your target table earlier in the process.And of course, for any new records you need to generate the necessary ID's. This will be what let's the SQL know what to insert/update. For the previous date, you may want to also collect that in this stage so you can use it in the Post SQL.

 

2. In the post SQL statement, I would then join the temporary table & target table based on the unique identifiers. You can first "practise" by doing a select query to join your target table to see that you'll select all the right records - and if you're happy with the result, do the UPSERT. You're static value is easy, the previous date should be present in your temporary table and you should be able to set the target column to match it. Sometimes this query can be tricky (more from a SQL syntax perspective), so I use forums to get it right when I'm stuck.

 

I hope that helps.

 

Regards,

Tom

Purvy85
6 - Meteoroid

Hi @TomWelgemoed 

 

Thank you very much for taking the time to work through this with me. I couldn't get this to work (through my error I feel) and was fortunate enough to get some one on one assistance on this.

 

The solution that has worked fantastically for me is using the normal output tool and setting the output option to one of the update methods depending on what you are doing. The primary key is identified, and if every value is provided it will replace the items which updates the required columns. 

Jason_Purvis_0-1585112044336.png

I'm not sure if this is the best method but it works perfectly for me and hopefully anyone else trying the same might find this works for them as well.

 

Thanks very much again for your help.

 

TomWelgemoed
12 - Quasar

Hi @Purvy85 ,

 

Think that's even better than using the post SQL method I described. Glad you got it working!

Labels