mysqlsqlselectsql-insertselect-into

Is there a way to consolidate this insert into select query?


Use case: For some particular test qa users stored in the users table, I want to update or create values in another table named user_limits so they have high limits of values 1000

users table definition:

create table if not exists users
(
    id int unsigned auto_increment
        primary key,
    email varchar(255) not null
)

user_limits definition:

create table if not exists user_limits
(
    user_id int unsigned not null,
    type_id smallint not null,
    remaining int not null,
    constraint user_limits_user_id_type_id_unique
        unique (user_id, type_id),
    constraint user_limits_user_id_foreign
        foreign key (user_id) references users (id)
);

My current query that works fine is:

insert into user_limits
(user_id, type_id, remaining)
select id, 1, 1000
from users
where email like 'qa-%mydomain.com'
    ON DUPLICATE KEY UPDATE remaining = 1000;

insert into user_limits
(user_id, type_id, remaining)
select id, 2, 1000
from users
where email like 'qa-%mydomain.com'
    ON DUPLICATE KEY UPDATE remaining = 1000;

insert into user_limits
(user_id, type_id, remaining)
select id, 3, 1000
from users
where email like 'qa-%mydomain.com'
    ON DUPLICATE KEY UPDATE remaining = 1000;

insert into user_limits
(user_id, type_id, remaining)
select id, 4, 1000
from users
where email like 'qa-%mydomain.com'
    ON DUPLICATE KEY UPDATE remaining = 1000;

It is repetitive, doing same thing for type_id values ranging from 1 to 4, and later on might have to do this for types from 1 to 10.

I tries to insert multiple values, but got syntax error because I have to select then insert. Any MySQL expert has input how to write a more elegant and shorter query ?

My MySQL version is 8.0.23


Solution

  • Something like

    INSERT INTO user_limits (user_id, type_id, remaining)
    WITH RECURSIVE cte AS ( SELECT 1 num
                            UNION ALL
                            SELECT num + 1 FROM cte WHERE num < @maximal_typeid_value )
    SELECT users.id, cte.num, 1000
    FROM users
    CROSS JOIN cte
    WHERE users.email LIKE 'qa-%mydomain.com'
    ON DUPLICATE KEY UPDATE users.remaining = 1000;