ON DUPLICATE KEY UPDATE with WHERE 条件
我使用 ON DUPLICATE KEY UPDATE 函数在单个表中更新/插入值。到目前为止一切都很好。
INSERT INTO table1 SET field1=aa, field2=bb, field3=cc
ON DUPLICATE KEY UPDATE SET field1=aa, field2=bb, field3=cc;
但现在我想实现仅当条件 (WHERE
) 为真时才进行更新。
语法不正确:
INSERT INTO table1 SET field1=aa, field2=bb, field3=cc
ON DUPLICATE KEY UPDATE SET field1=aa, field2=bb, field3=cc WHERE field4=zz;
您知道正确的 SQL 语句是怎样的吗?
多谢。
I update/insert values in a single table with the ON DUPLICATE KEY UPDATE
function. So far everything is fine.
INSERT INTO table1 SET field1=aa, field2=bb, field3=cc
ON DUPLICATE KEY UPDATE SET field1=aa, field2=bb, field3=cc;
But now I would like to achieve that the update only is done if a condition (WHERE
) is true.
Syntactically not correct:
INSERT INTO table1 SET field1=aa, field2=bb, field3=cc
ON DUPLICATE KEY UPDATE SET field1=aa, field2=bb, field3=cc WHERE field4=zz;
Any ideas how the correct SQL statement is?
Thanks a lot.
使用 IF() 应该可以工作,尽管它不太好:
只有在满足条件的情况下才用新值更新字段,否则保留旧值。
Using IF() should work, though it's not nice:
Only update the fields with new values if the condition is met, otherwise keep the old ones.