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
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;