Insert If duplicate not found in table else update

Question

I wanted to ignore or update duplicate when I am going to insert values. I know about on duplicate key but i can't figure out the solution with that. Here is sample table example.

| ID   | roll     | sub   | mark |
| ---- |----------| ------|------|
|   1  | 100      | 11    |  15  |
|   2  | 101      | 11    |  16  |
|   3  | 102      | 11    |  17  |
|   4  | 100      | 12    |  10  |
|   5  | 101      | 12    |  11  |
|   6  | 102      | 12    |  12  |

Here the id is primary key but I wanted to insert to check if roll & sub already exist then update otherwise insert new row. I've tried with the following code but that's insert duplicate row but it should update row 6 in following table.

CREATE INDEX mycompo_index on student(roll,sub);

insert into student(roll, mark, sub)
            values (102, 22, 12)
on duplicate key update mark = values(mark);

Answer 1

If the combination of roll and sub should be unique, you should define such a key in your table:

ALTER TABLE student ADD CONSTRAINT student_uq UNIQUE(roll, sub)

Note that if you do this, you don't have to explicitly create the index you're creating, the constraint will create on for you. Once you have this is place, you can use the on duplicate key syntax you were trying to use:

INSERT INTO student(roll, mark, sub)
VALUES (102, 22, 12)
ON DUPLICATE KEY UPDATE mark = VALUES(mark)