SQL insert multiple select in one query-CodePudding

I have a code for adding/updating records in the database. This works for a single entry.

insert into skills_to_user (id, user_id, skill_id, value)
select stu0.id, 4983, 3, 50 from skills_to_user stu0 where stu0.user_id = 4983 and stu0.skill_id = 3
ON DUPLICATE KEY UPDATE value = VALUES(value);

What do I want to get?

insert into skills_to_user (id, user_id, skill_id, value)
values (8, 4983, 3, 50),
       (9, 4983, 4, 40),
       (10, 4983, 5, 30)
ON DUPLICATE KEY UPDATE value = VALUES(value);

When I try to use multiple select I get an error.

insert into skills_to_user (id, user_id, skill_id, value)
values (select id from skills_to_user where user_id = 4983 and skill_id = 3, 4983, 3, 50),
       (select id from skills_to_user where user_id = 4983 and skill_id = 4, 4983, 4, 40),
       (select id from skills_to_user where user_id = 4983 and skill_id = 5, 4983, 5, 30)
ON DUPLICATE KEY UPDATE value = VALUES(value);

That didn't work either

insert into skills_to_user (id, user_id, skill_id, value)
    select stu0.id, 4983, 3, 50 from skills_to_user stu0 where stu0.user_id = 4983 and stu0.skill_id = 3,
    select stu1.id, 4983, 4, 40 from skills_to_user stu1 where stu1.user_id = 4983 and stu1.skill_id = 4,
    select stu2.id, 4983, 5, 30 from skills_to_user stu2 where stu2.user_id = 4983 and stu2.skill_id = 5
ON DUPLICATE KEY UPDATE value = VALUES(value);

Initially, the table looks like this photo before

After the update, I expect the first record to update and 2 new records will be added. photo after

P.S. The id is initially unknown, I can't use the exact id value. Therefore, I need to do a select and if there is such an id, I update the entry in the database, and if there is none, I create it.

-- auto-generated definition
create table skills_to_user
(
    id           int auto_increment
        primary key,
    user_id      int                                not null,
    skill_id     int                                not null,
    value        int                                null,
    date_add     datetime default CURRENT_TIMESTAMP null,
    date_updated datetime                           null,
    constraint skills_to_user_id_uindex
        unique (id)
);

CodePudding user response：

try

insert into skills_to_user (id, user_id, skill_id, value)
select stu0.id, 4983, 3, 50 from skills_to_user

CodePudding user response：

I am not seeing the problem here given

DROP TABLE IF EXISTS T;

create table t
(id int auto_increment primary key,
user_id int, skill_id int, value int);

alter table t add unique key uk1(user_id,skill_id);

insert into t(user_id, skill_id, value)
values (4983, 3, 50),
       (4983, 3, 40),
       (4983, 4, 60),
       (4983, 5, 30)
ON DUPLICATE KEY UPDATE value = VALUES(value);

select * from t;

 ---- --------- ---------- ------- 
| id | user_id | skill_id | value |
 ---- --------- ---------- ------- 
|  1 |    4983 |        3 |    40 |
|  2 |    4983 |        4 |    60 |
|  3 |    4983 |        5 |    30 |
 ---- --------- ---------- ------- 
3 rows in set (0.001 sec)

CodePudding user response：

I don't understand what you're asking, but if you want to add multiple inputs on the same query is something like.

INSERT INTO table_name VALUES ('test1','test2','test3','test4','test5','test6','test7','test8'),('test1','test2','test3','test4','test5','test6','test7','test8')

CodePudding user response：

Try this one to keep id for existing user_id and skill_id

INSERT INTO skills_to_user (id, user_id, skill_id, value)
SELECT t.id, f.user_id, f.skill_id, f.value
FROM ( 
  SELECT 4983 user_id, 3 skill_id, 50 value
  UNION ALL
  SELECT 4983 user_id, 4 skill_id, 40 value
  UNION ALL
  SELECT 4983 user_id, 5 skill_id, 30 value
) f 
LEFT JOIN skills_to_user t ON f.user_id = t.user_id AND f.skill_id = t.skill_id
ON DUPLICATE KEY UPDATE value = VALUES(value)