Is it possible to update or insert if not exist in MySQL using where-in clause without primary key?

Below is my database table.

User
id       role        type     name
1        1           1        John
2        2           1        Doe

Below is my data:

role = 1
type = 1
name = HelloWorld

role = 1
type = 2
name = HelloWorld

rule = 3
type = 1
name = HelloWorld

I want the following result in my database table.

User
id       role        type     name
1        1           1        HelloWorld // updated name because role = 1 and type = 1 exist.
2        2           1        Doe
3        1           2        HelloWorld // inserted name because role = 1 and type = 2 do not exist.
4        3           1        Helloworld // inserted name because role = 1 and type = 2 do not exist.

How to write MySQL query without executing select query first? In my case there is no primary key.

Answer

You can use MySQL insert ... on duplicate key update ... syntax:

insert into mytable (role, type, name)
values (1, 1, 'Hello World')
on duplicate key update name = values(name)

For this to work, you need to set up a unique key constraint on columns (role, type). Create it if it doesn’t yet exist:

alter table mytable add constraint mytable_unique_role_type unique (role, type);

This syntax can also be used to process multiple inserts at a time:

insert into mytable (role, type, name)
values (1, 1, 'Hello World'), (1, 2, 'Hello World'), (3, 1, 'Hello World')
on duplicate key update name = values(name)