SQL MERGE MySQL: counting users by name

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

Suppose we want to know how many users have the same name. Let us create table users as follows:

create table users(
    id int primary key auto_increment,
    name varchar(8),
    count int,
    unique key name(name)
);

Now, we just discovered a new user named Joe and would like to take him into account. To achieve that, we need to determine whether there is an existing row with his name, and if so, update it to increment count; on the other hand, if there is no existing row, we should create it.

MySQL uses the following syntax : insert … on duplicate key update …. In this case:

insert into users(name, count)
       values ('Joe', 1)
       on duplicate key update count=count+1;


Got any SQL Question?