Update column value

[nbt1032]

I have a DB table that I would like to update a single column value of - it is a non-primary key field.  The workflow is creating a list with 2 columns - first column has the primary key of table to update, 2nd column has the value of the field to update in the DB table.

In SQL I would do this with a INSERT with INNER JOIN. 

Does anyone have advice on how to do this in Alteryx.

Thx. Marc.

[Bob_Blackey]

Hi Marc,

Based on the way I read your questions I think the following should solve.

This reads in the updates and replaces the matching values in ColA of the database.

In the JOIN the J records are the Inner Join, only where the SKU matches. In the JOIN TOOL I replace the original ColA with the updated ColA.

Then in the UNION you merge those records with the unmatched records from the DB.

The workflow is attached.

Cheers,
Bob

[nbt1032]

Bob.  Almost there.  I was able to get to the same end point as the sample.  The next step is to update the database. 

My workflow is almost identical, but bigger.  The result is a unique key value, and a new updated column value.    How do I update the database is the issue.  

One way I know will work is to write the output to a temporary table, the on the POST SQL, do an UPDATE INNER JOIN statement, then drop the temp table.

Is there an easier way to do this in Alteryx?  If I use the output object and point it at a database table, I though I could do this with the output options - selecting the update.  It will not allow me to map the field to update.

This is where I am stuck. 

Thx,

Marc.

[SeanAdams · Accepted solution]

Hey @nbt1032

There are 2 ways to do this in alteryx:

- Do a specific update for just one field.    To do this you will probably need to pop this into a defined temp table (as you said) and then invoke either a stored proc, or a simple update..from query.

- Alternatively - you can pull the entire record into Alteryx (i.e. if you have 20 fields - bring all in) and then write all 20 fields back, with one updated using an output tool and in Output Options instead of overwrite or append you choose "Update: insert if new".

This is definately the least-painful way to do it, and the only bad thing about this approach is if you have to update thousands of records this may slow down (and tax your server as it reevaluates all the indecies).

Note: You should log an idea about a basic "Update" tool in the ideas section - I'd support this!

https://community.alteryx.com/t5/Alteryx-Product-Ideas/idb-p/product-ideas

[nbt1032]

Sean. 

You have confirmed my thoughts on this.  I do have large volumes to update.  Your second option, as you mentioned, would just be too heavy on a larger dataset.

I am going to stick with the SQL approach.  The nice thing about writing your own SQL on a large update is that you have options on how to tune it up.  Running it through any blackbox such as Alteryx can sometimes be frustrating.

Thx.

[Bob_Blackey]

Thanks @SeanAdams for the follow-thru. Good to have so many resources when we can't get back to the forum.

Glad your found the full solution @nbt1032 !

[jholt]

Thanks for this input.  I'm currently evaluating Alteryx for my organization and I am shocked that there is not a way to perform a simple update based on a join.  More than once I've muttered to myself, "this would be so simple in SQL," which I imagine is the antithesis of the experience Alteryx wants to provide.

If I've overlooked something, please let me know!

[Dave_M]

Yes, I agree, while I like the simplicity of using alteryx for many operations is would be nice to have a simple sql box to throw into a workflow so don't do things mysteriously outside the workflow.

[TempTemp]

You can do the pre-sql in the Output Data tool