MySQL write via ODBC

[michaelgreer]

I have a standard interface to various MySQL databases that have the same table format.  In one instance I am getting duplicate rows when I write the same record rather than overwriting.  The MySQL database is not under my control.  It is my understanding the PxPlus handled the determination of whether to do an update or an insert.  I am opening the table with a unique key specified. The insert/duplication occurs whether I write with a key or not.  Is there some open option that I did not notice that will help with this behavior?

[Mike King]

The system generally determines if the record already exists by detecting an error on an INSERT SQL command.  Make sure your table definition includes a constraint on the primary key that it must be unique.

[Devon Austen]

If you know that you are updating a record and not inserting a new one you can use the UPDATE directive instead of the WRITE. This would be more efficient as well as PxPlus would not have to try and do an insert fail and then do an update.

[michaelgreer]

Curiously, I just tried the update option and it a) created a second row b) added a row after I had deleted the existing record(s).  This must be telling us something about the target table and how it is configured.