Start a conversation

MySQL duplicates row when using UpdateExistingAppendIfNotFound

When using TracerPlus Connect to make an ODBC connection to a MySQL Database/Table, you may experience unexpected data row INSERTS when using the UpdateExistingAppendIfNotFound update option.

When using this update option with a MySQL database, a new row may be added when not expected since it already exists based on the defined Update Based On definition in TracerPlus Connect.

If the row data has not been changed prior to update to MySQL, MySQL returns an error indicating the row is not found.  TracerPlus Connect treats this the same as the row not existing (since it is the same error generated by MySQL) and proceeds to insert the row as NEW (Append if not found).

To fix this, you can configure your MySQL ODBC driver properties to "Return matched rows instead of affected rows".  This is a checkbox option under the "Cursors/Results" tab exposed by clicking the of the Details >> button when editing your driver in the PC ODBC Data Source Administrator tool.

Setting this option ON allows MySQL to correctly report the existence of the row in question even though the data has not changed.

Note: This setting may be located under a different tab depending on the version of the MySQL ODBC driver being used. In some cases we have seen this appear under a Flags1 tab.

Choose files or drag and drop files
Was this article helpful?
  1. Dan Peluso

  2. Posted
  3. Updated