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

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...