CODEWITHSUNDEEP
mysqlsqlselectsql-insertselect-into
Kristi Jorgji
Kristi Jorgji

Reputation: 1739

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

Upvotes: 1

Views: 26

Answers (1)

Akina
Akina

Reputation: 42661

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;

Upvotes: 1

Related Questions