Adijm Adijm - 9 months ago 54
MySQL Question

MySQL | Update records insert of insert if field exists

I have a table with three fields:

  • Auto increment integer (primary key)

  • String which should be unique

  • integer containing a value which is not unique

See picture here:
enter image description here

The value for the integer field does not need to be unique.

My problem:
I pull data from an API and insert the string and third field (integer) into the table. At the moment i am truncating the table and inserting again to avoid duplicates. How do I do it so if the string is existing, it just updates the third field (integer), if it does not exist it will insert the row as normal.

I am aware of the ON DUPLICATE KEY technique however my 'key' is the primary key and it is the string field that i want to check for duplicates.

Answer Source

If the string field should be unique, then declare it as unique:

alter table add constraint unq_table_string unique (string);

Then, on duplicate key will find a match when this constraint is also violated.