Create a relation for each record in a table

Admin 1
Viewed 48

I have a table of users in an SQLite database, as well as a few other tables: roles, and userRoles. userRoles contains a foreign key relation linking user IDs to roleIDs. Each user can have 0-to-many roles.

I have an existing database with a user table, and I have added the role tables after the fact. I need a query that will add each user to a "default" role.

TO do this, I have written a query:

INSERT INTO userRoles (roleID, userID)
SELECT id from users, SELECT id from roles WHERE roleName = 'default';

I have also tried specifying the values:

INSERT INTO userRoles (roleID, userID) VALUES (
    SELECT id from users,
    SELECT id from roles WHERE roleName = 'default'
);

However it is incorrect. Specifically SQLite is telling me:

Error: in prepare, near "SELECT": syntax error (1)

This is SQLite version 3.37.2

sqlite sql
1 Answers

You can SELECT only from the table users and use a scalar subquery to get the id of the 'default' role name:

INSERT INTO userRoles (userID, roleID)
SELECT id, (SELECT id FROM roles WHERE roleName = 'default') FROM users;

or, with a join:

INSERT INTO userRoles (userID, roleID)
SELECT u.id, r.id 
FROM users u JOIN roles r
WHERE r.roleName = 'default';
user_104988280
Related