Insert or Update MySQL database if 3 fields match [duplicate]

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!

Answer 1

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.