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
Solved! Go to 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)
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
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...
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
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.
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.