Using "on duplicate key" with multiple duplicate condition

Question

I have table contains questions data. Also every question has test id, document id (dokuman_id), order (sira) and video data. I want to create a query that insert new row if there isn't a row has same test id, document id and order; else just update video field.

I know I could use ON DUPLICATE KEY if I want to do it with id(primart_key) column. But I need to control 3 duplicate column conditions as optimum as possible.

So create a combined unique index over all three columns …? — CBroe, Aug 11 '17 at 14:19

score 1 · Accepted Answer · answered Aug 11 '17 at 14:24

1

you should create index on test_id , dokuman_id , sira

INSERT INTO table (test_id , dokuman_id , sira , video) 
VALUES(:test_id , :dokuman_id , :sira , :video) 
ON DUPLICATE KEY UPDATE video = :video

answered Aug 11 '17 at 14:24

Ali Faris

17,754
10
45
70

Do I have to delete primary key before this? – eneskomur Aug 11 '17 at 14:26
no you don't have to delete the primary key , you just create another index – Ali Faris Aug 11 '17 at 14:27
Ok thank you mate. But I just don't understand how db decides key for **on duplicate key**. There can be many keys – eneskomur Aug 11 '17 at 14:30
3

@nskmr The engine checks **every** unique key (including the primary key) for duplicates. – Paul Spiegel Aug 11 '17 at 14:41

Using "on duplicate key" with multiple duplicate condition

1 Answers1