MySQL on duplicate key on a non-unique field

Question

I have a table containing all sort of parameters. The structure of the table is : id, object_id, param_name, param_value

The following code works, but it appends results instead of updating them. The fact is that I can't use ON DUPLICATE KEY because my fields are non-uniques (except for id of course)

INSERT INTO `params_table` (`object_id`, `param_name`, `param_value`)
SELECT
A.id AS my_object_id,
'XYZ' AS my_param_name,
IF(TMP.abc IS NULL,0,1) AS my_param_value
FROM
ref_table AS A
LEFT JOIN tmp_table AS TMP ON TMP.abc = A.abc
ON DUPLICATE KEY
UPDATE `param_value` = IF(TMP.abc IS NULL,0,1);

Answer 1

The ON DUPLICATE KEY clause does not only work on the primary key:

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, an UPDATE of the old row is performed

So unless I'm missing something obvious you simply need to create a unique index on the column combination you want to make unique:

ALTER TABLE params_table
ADD UNIQUE unique_object_param(object_id,param_name);