How to update data if unique value already exists?

Question

How can I update a unique value if it exists in my table, but add it if it doesn't? In my sql here, OBJECTID is my primary key. I'm comparing two data tables, and then appending the records to a table LatestData where the values in OctoberExport do not match the values in SeptExport.

INSERT INTO LatestData ( OBJECTID, LINING_MATERIAL )
SELECT OctoberExport.OBJECTID, OctoberExport.LINING_MATERIAL
FROM OctoberExport
WHERE (((OctoberExport.OBJECTID) Not In (SELECT SeptExport.OBJECTID FROM SeptExport WHERE OctoberExport.OBJECTID = SeptExport.OBJECTID AND OctoberExport.LINING_MATERIAL = SeptExport.LINING_MATERIAL)));

Possible duplicate of [Insert to table or update if exists (MySQL)](http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql) - Please use the [Search](http://stackoverflow.com/search) feature before posting a question. — Pred, Jan 21 '15 at 16:02
Question title suggests a simple case of on duplicate key update, but the query suggests something else, so it's not clear what you really need. Maybe post some example data with desired output? — piotrm, Jan 21 '15 at 16:13

score 0 · Answer 1 · answered Jan 21 '15 at 16:01

There is a function in MySQL called on duplicate. You can tell your query to add the value if it's not there, if it is there insert it.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

Example (taken from mysql.com):

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

score 0 · Answer 2 · answered Jan 21 '15 at 16:41

The PRIMARY KEY constraint enforces uniqueness. Since the primary key of the table is the OBJECTID column, it's not possible to insert another row with the same OBJECTID value.

In order to have two rows with the same OBJECTID value in the table, you would need to change the PRIMARY KEY of the table to include some other column (or columns) to make it unique.

For example, you could add another column EXPORT_MONTH. Rows from the SeptExport table would have one value, and rows from the OctExport table would have a different value, so both rows could be added to the table.

ALTER TABLE LatestData ADD EXPORT_MONTH VARCHAR(12) NOT NULL DEFAULT '';

You can set the value of the existing rows to something other than empty string using an UPDATE statement.

Changing the PRIMARY KEY constraint can be a bit more complicated, especially if you have foreign key references. But you'll need to end up with a primary key constraint like this:

PRIMARY KEY (OBJECTID, EXPORT_MONTH)

(Sometimes, creating a new table, and then copying rows from the existing table, and the renaming the tables is the way to go to make a change like this.)

Net result is that you could have rows like this in your table:

OBJECTID  EXPORT_MONTH  LINING_MATERIAL
--------  ------------  ---------------
   12345  SeptExport    Aluminum
   12345  OctExport     Platinum

(I'm not exactly clear on what you're asking, but given OBJECTID has a unique constraint, it's not possible to have two rows with the same value in OBJECTID.)

How to update data if unique value already exists?

2 Answers2