CODEWITHSUNDEEP
mysqlsqldatabase
Jonas
Jonas

Reputation: 131

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

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   |
+----+----------+-------+

Upvotes: 1

Views: 2050

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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)
);

Upvotes: 2

Related Questions