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.

Update Rows using Write Data In-DB tool

SSteckler
5 - Atom

 

 

I am trying to update existing rows in a database table.  I can do it by streaming my data out and using the Output Data tool (so I know I have the right permissions, etc), but I can't figure out how to use the Write Data In-DB tool to do the same thing.  I don't even see the Update Rows option in the Output Mode dropdown menu (I have updated to version 2019.1).  I've checked that the field names, and number of fields all match.  What  am I missing?

Write InDB.PNG

 

10 REPLIES 10
Inactive User
Not applicable

In-DB Tool does not have delta logic capability. You will see by default when you create your connection the output it suggests using Bulk Loaders as this function is not available. I would stream out and perform the delta logic on the PK as you have been.

SSteckler
5 - Atom

I’m not sure what you mean by delta logic – the documentation on the Write Data In-DB tool indicates the update rows is possible (and I think it works for SQL in particular) and the query structure examples on the page are similar to what we would write manually. Would changing the write driver help in this case? What determines if “Update Rows” appears or not with this tool?

acowpy
8 - Asteroid

@SSteckler did you figure this out?

 

I am having the same problem. I found the original discussion of this feature and confirmation that it should be present as of 2018.4, but I can't see the 'Update Rows' and 'Delete Rows' options that are supposed to exist..

 

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/In-Database-Update-and-or-Delete/idc-p/19284...

SSteckler
5 - Atom

The only advice I have got is to use the Output Tool to write to the database.  This solution works fine (maybe a little slow, but my data sets are relatively small), but I've never got an answer on why the "Write Data In-DB" tool won't do it.

warrencowan
9 - Comet

@acowpy @SSteckler This one really ground my gears too but I did some small tests with various options and I think I 'may' have solved it to an extent, so am sharing just in case it helps. 

 

I found writing to a temporary table first, just prior to writing back to the source (now the target) table, saw the records reload to the table in their modified/updated form. This behaviour was consistent across both 'delete & append' and 'drop table' modes in the final write. I suspect this is holding the records safely in temp, whilst the table is dropped/truncated and without refreshing what you want to reload, but can't be sure so no warrenties supplied 🙂

 

alteryx IN DB.PNG

Caveat ... this was a small scale test with a handful of records and an overwrite scenario rather than an incremental update one, but hopefully this might kick off some solutionising by others.

 

 

ZMcCain
6 - Meteoroid

@warrencowan This still doesn't answer the question as to why 'Update Row' and 'Delete Rows' do not show up in the tool. 

It's sad that we are at version 2019.4.6 and we still do not have a working Write In-DB tool, nor do we have valid documentation for this tool - if it isn't going to show up, fine, but at least take it out of the documentation. 

 

Documentation: https://help.alteryx.com/2019.4/LockInOutput.htm

ntobon
Alteryx
Alteryx

@ZMcCain 

Write Data In-DB Tool - options:
Update Rows: Updates existing rows in a table based on the incoming records.
Delete Rows: Deletes existing rows in a table based on the incoming records.
Those options are only available when connecting to SQL Server via ODBC. 

Help: https://help.alteryx.com/current/designer/write-data-db-tool
Under "Limitations" it says "Update/Delete is currently only supported for SQL Server ODBC connections"

simonaubert_bd
13 - Pulsar
kosie-snyman
7 - Meteor

I am using the Write Data in-DB Tool in version  Version: 2022.1.1.25127 and notice that the Update is available after completing the first run, If yu then input a second file you need to specify a WHERE clause but I cannot fugure out how to create the WHERE clause. Any ideas how to create the WHERE clause?

 

Labels