INSERT on update. Compound Key

Question

I have a question on This MySQL statement.

I got this from this post

However, I am not sure how to structure the query when I have the table like this.

     TableA
     --------
     id ==> Auto Increment field
     question_id
     form_id
     member_id
     event_id
     admin_id 
     desc
     is_cand_field
     c_field_id

In this, question_id, form_id, memmber_id, event_id, admin_id can be same. desc, is_cand_field, c_field_id can change. When inserting, I want to check if this combination exists. If exists, I want to update the last 3 fields. If not, I want to insert a record.

Is this possible with the above referred query. Its not clear from the page.

`desc` is a bad name for a column. It will give you problems if you forget to quote it. — Mark Byers, Aug 21 '12 at 19:31
I just made up these names. Real names are different, though! Thanks for the comment anyways! — Kevin Rave, Aug 21 '12 at 19:32

score 1 · Accepted Answer · answered Aug 21 '12 at 19:42

1

Add unique key on combination of these fields:

ALTER TABLE `tablea` ADD UNIQUE INDEX `compound_key` (`question_id`, `form_id`, `member_id`, `event_id`, `admin_id`);

Insert values and add update section:

INSERT INTO tablea (`question_id`, `form_id`, `member_id`, `event_id`, `admin_id`, `desc`, `is_cand_field`, `c_field_id`)
VALUES (1, 2, 3, 4, 5, 1, 0, 1)
ON DUPLICATE KEY UPDATE `desc` = VALUES(`desc`), is_cand_field = VALUES(`is_cand_field`), c_field_id = VALUES(`c_field_id`)

answered Aug 21 '12 at 19:42

dev-null-dweller

29,274
3
65
85

I don't think you need `VALUES` in the `ON DUPLICATE KEY UPDATE`. – gen_Eric Aug 21 '12 at 19:44
It's better than repeating values - when you have parametrized query you don't have to double bind them. Also you can insert /update multiple rows with their respective values in single query, no need for one query per row. – dev-null-dweller Aug 21 '12 at 19:52
I was thinking there is a way to do this without even adding the compound key to the table. But anyways, this is going to be the only relevant answer – Kevin Rave Aug 21 '12 at 19:59
Without adding unique key you would have to query database and find if there is a record with those values, like @Tornike posted, but that way you don't use `ON DUPLICATE KEY` statement. – dev-null-dweller Aug 21 '12 at 20:04
@dev-null-dweller Yes, I am aware of the other route. But I thought there might be some flexibility in the statement itself to allow specifying the unique keys. Thanks anyways! – Kevin Rave Aug 21 '12 at 20:36

score 0 · Answer 2 · answered Aug 21 '12 at 19:35

0

You can add multiple rows in the ON DUPLICATE KEY UPDATE section.

INSERT INTO TableA (form_id, member_id, event_id, admin_id, desc, is_cand_field, c_field_id)
VALUES (2, 3, 4, 5, 'b', 0, 1)
ON DUPLICATE KEY UPDATE desc='a', is_cand_field=1, c_field_id=2

answered Aug 21 '12 at 19:35

gen_Eric

223,194
41
299
337

It does not check if this combination `question_id, form_id, memmber_id, event_id, admin_id` exists or not. Or does it? – Kevin Rave Aug 21 '12 at 19:37
Only if that combination is a UNIQUE key in the database. – gen_Eric Aug 21 '12 at 19:43

INSERT on update. Compound Key

2 Answers2