Insert or Update MySQL database if 3 fields match

Question

I have the following schema

CREATE TABLE `list` (
  `name` text NOT NULL,
  `value` text NOT NULL,
  `id` int(11) NOT NULL,
  `note` text,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and I would like to INSERT a new row into the database if a row with given name, value and id don't exist, otherwise I would like to update the note field. My insert query looks as follows:

INSERT INTO list (name, value, id, note) 
VALUES("test", 2, 221222, "this is a test")

I am not sure what to do, as I don't have a primary key in the list and haven't been able to figure out how to set unique fields. Thank you in advance!

What can be the maximum character lengths of `name` and `value` fields ? Why not just define them as `varchar` ? If these fields are varchar, then you can easily define a UNIQUE constraint, or even better would be PRIMARY key on the combination of `(name, value, id)`. Afterwards, you can employ [`IODKU`](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) — Madhur Bhaiya, Sep 13 '19 at 17:35
Note: MyISAM is an obsolete database engine, try and use InnoDB, and `latin1` character columns also cause huge problems for pretty much everyone, `utf8mb4` is a safer default. — tadman, Sep 13 '19 at 17:35
I have updated my name and value fields to varchar, set Primary, and IODKU works perfectly well now. Thank you! — Jani, Sep 13 '19 at 17:40
`REPLACE INTO db (` with `VALUES (` ? I think this would allow you to not only add the values, but also permit you to *change* some of the values as suggested in an earlier comment. — somebody, Sep 13 '19 at 17:52

score 1 · Answer 1 · answered Sep 13 '19 at 18:46

I would like to insert a new row into the database if a row with given name, value and id don't exist.

Option 1

You can write a query that checks if the record already exists before inserting it:

INSERT INTO list (name, value, id, note) 
SELECT 'test', 2, 221222, 'this is a test'
WHERE NOT EXISTS (
    SELECT 1 
    FROM list 
    WHERE name = 'test' AND value = 2 AND id = 221222 AND note = 'this is a test'
);

The downside of this approach is that you need to specify the values to insert twice. The advantage is that it does not require you to alter the table strucutre, hence it won't have side effects on other potential queries.

Option 2

You can take advantage of MySQL INSERT ... ON DUPLICATE KEY syntax. First of all, you would need to create a composiye unique constraint on all 3 columns:

ALTER TABLE list ADD UNIQUE list_unique_index(id, name, value);

This actually prevents duplicates from being inserted (by any mean). Then you can express the query as follows:

INSERT INTO list (name, value, id, note) 
    VALUES("test", 2, 221222, "this is a test")
    ON DUPLICATE KEYS UPDATE id = id

If the unique constraint validation fails, MySQL executes the UPDATE id = id clause, which is basically a no-op.

Note: there is a third option that consists in using INSERT ... IGNORE. I would not recommend that since that actually hides all error that could happen in the query (not only those related to the unique constraint); this behavior can bite you somehow.

Insert or Update MySQL database if 3 fields match

1 Answers1