CODEWITHSUNDEEP
sqlmysql
Xeoncross
Xeoncross

Reputation: 57294

MySQL - Create an UNIQUE index on two columns for ON DUPLICATE KEY

I have a table with 4 columns. I want to be able to INSERT or UPDATE the value column on creation (I don't care about indexes row ID's).

CREAT TABLE tablename (
 id,   (primary)
 user_id, (index)
 setting_id, (index)
 value  (index)
);

Originally I was going to run a "REPLACE INTO..." query each time I wanted to save a value. But then I read about the problems and instead chose a INSERT INTO... ON DUPLICATE KEY UPDATE.

The problem is that I don't have single column for the "INSERT INTO... ON DUPLICATE KEY UPDATE" to use to figure out if this is a new row or if there is an existing row that needs to be updated.

So I figured that I would use a UNIQUE key that was a mix of the two columns that made this row unique (

CREATE UNIQUE INDEX index_name ON table(user_id, setting_id);

However, I'm not sure how to advance from here. How do I structure my queries to check this new INDEX when trying to figure out a row already exists with these two column values?

INSERT INTO `tablename` (value, user_id, setting_id) VALUES (1,34,15) ON DUPLICATE KEY UPDATE

:EDIT:

By removing the ID primary column leaving only (setting_id, user_id, & value) I was able to create a PRIMARY index on (setting_id, user_id) and then the following query worked. more help here.

INSERT INTO tablename (user_id, setting_id, value)
VALUES (42, 1, 12)
ON DUPLICATE KEY UPDATE value = 12

Upvotes: 5

Views: 10275

Answers (1)

Chris Gutierrez
Chris Gutierrez

Reputation: 4755

The query you have would end up looking something like this...

INSERT INTO table (value,user_id,setting_id) VALUES (1,34,15)
  ON DUPLICATE KEY UPDATE value = 1, user_id = 34, setting_id = 15;

Upvotes: 8

Related Questions