CODEWITHSUNDEEP
sqloracle11goracle-sqldeveloper
Evil Washing Machine
Evil Washing Machine

Reputation: 1341

Insert values into table B based on column from table A?

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?

Upvotes: 0

Views: 409

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Use insert . . . select:

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

Upvotes: 2

Related Questions