Insert multiple rows in role table based on condition

Question

I have two tables one is role and another one is user_role. User_role table contains all users of particular role. Now, i added two extra new roles into the role table. I want to insert the rows into user_role table who has role_id=4 with the new role_ids. I cannot update exsting rows because i want exsting rows also means the users who have role_id=4 also belongs to new role_ids. I tried in this way

INSERT INTO table2 (all_links, fields_one, fields_two) 
select URI, fields, details FROM table1 
WHERE date > "12-11-2013 00-00-00";

But here two tables are there, but in my case only one table is there. And in user_role table id is sequene so cant select from another table dirctely and do insert.

Please help on these.

Vikdor Vikdor · Accepted Answer · 2014-12-15T11:13:38

You can select and insert records into the same table.

If I understood your use case correctly,

INSERT INTO user_role (role_id, <other columns>)
SELECT <new_role_id>, <other columns>
FROM 
    user_role
WHERE
    role_id = 4.

This will insert all the existing records in user_role table with role_id as 4, into the same table with a different role_id. HTH.

Insert multiple rows in role table based on condition

1 Answers