i want to insert rows IF a row containing the specific values exists, and if not update it.
如果存在包含特定值的行，我想插入行，如果不更新的话。
Concretely:
具体:
A column user_id=5, user_zip=12345, distance=600 exists on the database.
列user_id=5, user_zip=12345，距离=600存在于数据库中。
If i try to insert user_id=5, user_zip=12345, distance=700 it should just update the distance
如果我尝试插入user_id=5, user_zip=12345, distance=700它应该只更新距离
but i try to insert user_id=5, user_zip=67890, distance=800 it should insert a new row.
但是我尝试插入user_id=5, user_zip=67890, distance=800它应该插入一个新的行。
I can't define the columns user_zip and distance unique, so that i can use the on duplicate key update.
我不能定义user_zip和distance惟一的列，因此我可以使用on duplicate key更新。
1 个解决方案
#1
3
I think you are misunderstanding how ON DUPLICATE KEY UPDATE works. With a unique constraint on (user_id, user_zip), this should work:
我想你误解了复制密钥更新的工作方式。对于(user_id, user_zip)有一个唯一的约束，这应该可以工作:
INSERT INTO yourTable (user_id, user_zip, distance) VALUES (5,12345,600)
ON DUPLICATE KEY UPDATE distance=600;
You don't have to define user_zip unique (that's how I understand your question), just the combination of user_id and user_zip. So you still can have 2 or more rows with the same user_id, just the user_zip can't match on those rows.
您不必定义user_zip unique(这就是我理解您的问题的方式)，只需定义user_id和user_zip的组合。仍然可以有两个或更多的行具有相同的user_id，只是user_zip不能匹配这些行。
#1
3
I think you are misunderstanding how ON DUPLICATE KEY UPDATE works. With a unique constraint on (user_id, user_zip), this should work:
我想你误解了复制密钥更新的工作方式。对于(user_id, user_zip)有一个唯一的约束，这应该可以工作:
INSERT INTO yourTable (user_id, user_zip, distance) VALUES (5,12345,600)
ON DUPLICATE KEY UPDATE distance=600;
You don't have to define user_zip unique (that's how I understand your question), just the combination of user_id and user_zip. So you still can have 2 or more rows with the same user_id, just the user_zip can't match on those rows.
您不必定义user_zip unique(这就是我理解您的问题的方式)，只需定义user_id和user_zip的组合。仍然可以有两个或更多的行具有相同的user_id，只是user_zip不能匹配这些行。