I have a table containing all sort of parameters. The structure of the table is : id, object_id, param_name, param_value
我有一个包含所有类型参数的表。该表的结构是：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)
以下代码有效，但它会附加结果而不是更新结果。事实是我不能使用ON DUPLICATE KEY因为我的字段是非唯一的（当然除了id）
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);
1 个解决方案
#1
6
The ON DUPLICATE KEY clause does not only work on the primary key:
ON DUPLICATE 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
如果指定ON DUPLICATE KEY UPDATE，并且插入的行将导致UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行的UPDATE
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);
#1
6
The ON DUPLICATE KEY clause does not only work on the primary key:
ON DUPLICATE 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
如果指定ON DUPLICATE KEY UPDATE，并且插入的行将导致UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行的UPDATE
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);