How can I add multiple rows referring to another table?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

you means add permission 5 to every single user this users in permission tables!