MySQL update record if two columns match data, otherwise insert row

Question

I have a simple MySQL table with three columns: "id" (INT), "property" (VARCHAR) and "value" (VARCHAR).

All the columns have to be able to have identical records (i.e. several records with the same id).

The ideas is to save different properties matching different ids.

My question is, how would I go about updating a record if my data matches both the ID and the Property, and otherwise add a new record?

I initially thought about using the ON DUPLICATE KEY, but that won't work, as none of my columns are unique.

Here's an example of the desired behavior:

1: I start with this table:

+----+----------+-------+
| id | property | value |
+----+----------+-------+
| 45 | money    |   500 |
+----+----------+-------+

2: I then insert: id = 45, property = sex, value = male, and I get:

+----+----------+-------+
| id | property | value |
+----+----------+-------+
| 45 | money    | 500   |
| 45 | sex      | male  |
+----+----------+-------+

3: I then insert: id = 45, property = money, value = 600, and I get:

+----+----------+-------+
| id | property | value |
+----+----------+-------+
| 45 | money    | 600   |
| 45 | sex      | male  |
+----+----------+-------+

4: Lastly, I can do this: id = 46, property = money, value = 600, and get:

+----+----------+-------+
| id | property | value |
+----+----------+-------+
| 45 | money    | 600   |
| 45 | sex      | male  |
| 46 | money    | 600   |
+----+----------+-------+

Possible duplicate of [Insert into a MySQL table or update if exists](https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — yash darak, Jul 28 '17 at 09:45

Tim Biegeleisen · Accepted Answer · 2017-07-28T10:18:09.050

Your logic implies that attempting to insert a new record whose id and property already appear in the table should instead cause that record's value to be updated. MySQL provides a way to do this. First add a unique constraint on the id and property columns:

ALTER TABLE yourTable ADD UNIQUE idx (id, property);

Then perform your inserts using this code:

INSERT INTO yourTable (id, property, value)
VALUES (45, 'money', 600)
    ON DUPLICATE KEY UPDATE value = 600;

If you wanted to create your table from scratch with a unique constraint you might try this:

CREATE TABLE yourTable (
    id int NOT NULL,
    property varchar(255) NOT NULL,
    value int,
    CONSTRAINT uc_idp UNIQUE (id, property)
);

Fantastic! How would I add this constraint when creating the table? — Jonas, Jul 28 '17 at 10:14

MySQL update record if two columns match data, otherwise insert row

1 Answers1