0

I want to batch insert into roles table below SQL to initialize user's role information, I grant three roles to every user:

INSERT INTO `roles` 
(`role_id`, `role_name`, `menu_id`, `role_updated_time`, `role_created_time`, `role_creator_user_id`, `role_sys_type`) 
VALUES  
(40, 'developer', '[1,2,3,4]', NULL, now(), 1027, 1),   
(41, 'financial', '[1,2,3]', NULL, now(), 1027, 2),     
(42, 'operation', '[1,2]', NULL, now(), 1027, 3);

I want to do this job in one SQL, so how to foreach every usr_id in users table so that it insert in roles table, the usr_id is equivalent to role_creator_user_id in roles table, and the role_id is auto_increment.

I use this SQL but not work:

INSERT INTO `roles` 
    (`role_id`, `role_name`, `menu_id`, `role_updated_time`, `role_created_time`, `role_creator_user_id`, `role_sys_type`) 
    VALUES  
    (40, 'developer', '[1,2,3,4]', NULL, now(), (select usr_id from users), 1),     
    (41, 'financial', '[1,2,3]', NULL, now(), (select usr_id from users), 2),   
    (42, 'operation', '[1,2]', NULL, now(), (select usr_id from users), 3);

So I tried this, also not work:

INSERT INTO roles (role_id, role_name, menu_id, role_updated_time, role_created_time, role_creator_user_id, role_sys_type) VALUES (select 100, 'developer','[1,2,3,4]',NULL,now(),usr_id,1 from users)

vishuB
  • 4,173
  • 5
  • 31
  • 49
Phoenix
  • 1,055
  • 1
  • 12
  • 27
  • 2
    I assume that "select usr_id from users" will return more than one entry. That might be the problem with your first variation, because in that statement you really just want one value. For the second version, try INSERT INTO `roles` (`role_id`, `role_name`, `menu_id`, `role_updated_time`, `role_created_time`, `role_creator_user_id`, `role_sys_type`) select 100, 'developer','[1,2,3,4]',NULL,now(),usr_id,1 from users – igorshmigor Sep 01 '15 at 06:44
  • @igorshmigor You mean remove the bracket? I tried, not work – Phoenix Sep 01 '15 at 06:49
  • It worked here on my database. What error do you get? Maybe some of the values are inconsistent with the table structure? Did you also remove the "Values" keyword? Because you have to remove that too, not just the brackets. – igorshmigor Sep 01 '15 at 06:51
  • Please see: http://stackoverflow.com/questions/9422529/mysql-how-do-you-insert-into-a-table-with-a-select-subquery-returning-multiple-r . It's just INSERT [...] SELECT [..], no VALUES – valicu2000 Sep 01 '15 at 06:57
  • @igorshmigor Yes, you are right, when I remove the `Values` keyword it works! Thank you! – Phoenix Sep 01 '15 at 08:52

0 Answers0