How to use ON DUPLICATE KEY UPDATE in MySQL without a UNIQUE index or PRIMARY KEY?

Question

The MySQL manual states:

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

What if you have a table that does not have a UNIQUE index or PRIMARY KEY, but you don't want to duplicate an entry for a column (say user_id).

Is there a way to do it in MySQL?

Answer 1

INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM `table` 
WHERE NOT EXISTS (SELECT * FROM `table` 
                  WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1

Source: How to 'insert if not exists' in MySQL?

Answer 2

Try this:

INSERT INTO `table` (userid)
SELECT 'value for userid' FROM `table` 
  WHERE userid = 'value for userid' HAVING count(*) = 0