Output Data Tool - update on unique rather than primary key?

[PeterGoldey]

Hi,

I'm updating back to a MySQL table.  The update key (a natural, unique key) is NOT the auto-increment primary key on the table.  

Is it possible to specific the key for update within the Output Data tool?  I see it will use a Unique Key if no Primary Key was specified on the table...but what if both exist?  Is there a way to force it to use one over the other?

Thanks!

Pete

[SeanAdams · Accepted solution]

Hey Peter,

You can't do this directly with the update table tool (the append/update if different only works on the primary key)  - but you can do this in a different way:

Method 1:

- pump this into a holding table - something like "Table_incoming".   For this table use the option to overwrite so that this table is always a clean record.

- then run 2 sql scripts (you can do this as a post-insert SQL statement on the output tool)

• one which deletes everything from your main table where the unque key combo exists in Table_incoming
• The next one then does a simple bulk insert from Incoming to your base table

Method 2:

as above, but invoke a stored procedure

Method 3:  Most common pattern for BI/analytics where database is tuned for read/insert, not update/delete

• Add a IsLatestVersion flag to your base table which is set to true for the latest version of every unique key
• On your input tool that does the read - do a post-read SQL that updates this latest flag to false
• Insert ALL the rows, making sure that you can spot the newer versions with a higher rowID; or a newer date
• Then call a SQL query which updates the latest rowID to 1 for the latest row version

Note: for large tables you'll need to do a few more pieces of work to make this perform, like not updating the latest flag for every row, only the incoming unique keys; doing the latest version as part of the insert; etc

@PeterGoldey  I really like your thought, but it's not supported directly - you should submit this as an idea, cc my name, and I'll definately support this idea.

If this gets you to a workable solution would you mind marking this as solved, if not- just reply on this thread and we can work it through as we go...

[PeterGoldey]

Thanks Sean!

I'd implemented a version of your method 1 already...its just a pain with a bunch of extra steps!

Added suggestion to Ideas - https://community.alteryx.com/t5/Alteryx-Product-Ideas/Output-Data-Update-Insert-if-New-allow-any-un...

- Pete

[Coxta45]

If the MySQL table isn't too large, just pull it into your workflow, Left Outer Join to your new data and use an output tool to simply append the Right (new) records from the join.

[PeterGoldey]

For the odd occurrence that would work (and i use that technique sometimes).  But its definitely a work around what should be a simple control in an existing tool.

In my case there are scores of tables, some quite large, and being MySQL there are no In-DB tools.   So that's not an efficient solution for this or most BI cases.