Question 21990 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I add multiple rows referring to another table? – Mysql

Let’s say I have two tables like below:

users table:

user_id name
0 kevin
1 alice
2 jake
3 mike

permissions table:

user_id permission
1 12
1 5
3 1

And let’s say that I want to add permission 5 to every single user who doesn’t already have it. What would be the best MySQL query for this?

Tags:

2

Answers


  1. 0

    Your question is not 100% clear whether you only need a query or you want to do inserts. Anyway, this query with NOT EXISTS can be used as base for all necessary actions:

    SELECT user_id, 5 AS permission 
FROM users u 
WHERE NOT EXISTS 
  (SELECT 1 FROM permissions 
   WHERE permission = 5 AND user_id = u.user_id);

    This will list all user id’s that haven’t yet a permission 5 and as second column, just 5 will be selected as permission.

    Then this result can be used for whatever it should be used. For example to add this outcome in a query to the already present entries, UNION ALL can be used:

    SELECT user_id, 5 AS permission 
FROM users u 
WHERE NOT EXISTS 
    (SELECT 1 FROM permissions 
    WHERE permission = 5 AND user_id = u.user_id)
UNION ALL
SELECT user_id, permission 
FROM permissions
ORDER BY user_id, permission;

    If – and I think this is your real question – the result of the NOT EXISTS query should be inserted into the permissions table, this insert command will do this:

    INSERT INTO permissions 
  (SELECT user_id, 5 
  FROM users u 
  WHERE NOT EXISTS 
    (SELECT 1 
    FROM permissions 
    WHERE permission = 5 AND user_id = u.user_id));

    You can try out these things here: db<>fiddle

    Login or Signup to reply.
  2. 0

    Below query will look for missing user id in permissions and insert new records in permissions table with a default permission equal to 5.

           INSERT INTO permissions
            (
                        user_id,
                        permission
            )
                   SELECT user_id,
                          5     AS permission
                   FROM   users  
                   WHERE  user_id NOT IN
                          (
                                 SELECT user_id
                                 FROM   permissions)
            )

    Sample result:

    user_id permission
    1 12
    1 5
    3 1
    0 5
    2 5
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search