根据表A中的列将值插入表B中? [英] Insert values into table B based on column from table A?

问题描述

我有2个表，一个Users表和一个User_permissions表.一个用户可以具有许多权限，并且可以将每个权限分配给许多不同的用户，尽管这种关系尚未配置到数据库中(不是我的决定！).

假设创建了2个新权限-admin权限和superadmin权限.此外，Users表中的每个用户都需要通过在User_permissions表中插入用户名和权限名称来获得此权限.

很明显，这可以由INSERT INTO User_permissions VALUES (userA, admin)手动完成，依此类推，但是鉴于我有一个超过1000个用户的列表，是否有更简单的方法来做到这一点?我当时想用Java编写快速脚本，但是有一种仅使用SQL的简便方法吗?

解决方案

使用insert . . . select:

INSERT INTO User_permissions(user, permission)
    SELECT user, 'admin'
    FROM users u;

I have 2 tables, a Users table and a User_permissions table. A user can have many permissions and each permission can be assigned to many different users, although this relationship has not been configured into the database (not my decision!).

Let's say 2 new permissions are created - an admin permission and a superadmin permission. Furthermore, every user in the Users table need to be given this permission by inserting the username and permission name into the User_permissions table.

Obviously this could be done manually by INSERT INTO User_permissions VALUES (userA, admin) and so on but given that I have a list of over 1,000 users, is there an easier way of doing this? I was thinking of writing a quick script in Java, but is there an easier way using only SQL?

解决方案

Use insert . . . select:

INSERT INTO User_permissions(user, permission)
    SELECT user, 'admin'
    FROM users u;

这篇关于根据表A中的列将值插入表B中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！