Update column value

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.

Common Use Cases

Accepted answers

SeanAdams

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

All comments

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

UpdateColumnValue.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703