CODEWITHSUNDEEP
mysql
user877167
user877167

Reputation: 419

MySQL: INSERT IGNORE or ON DUPLICATE KEY UPDATE with checking multiple and not unique columns

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.

If i try to insert user_id=5, user_zip=12345, distance=700 it should just update the distance

but i try to insert user_id=5, user_zip=67890, distance=800 it should insert a new row.

I can't define the columns user_zip and distance unique, so that i can use the on duplicate key update.

Upvotes: 3

Views: 1302

Answers (1)

Jacob
Jacob

Reputation: 43229

I think you are misunderstanding how ON DUPLICATE KEY UPDATE works. With a unique constraint on (user_id, user_zip), this should work:

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.

Upvotes: 3

Related Questions