Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to update multiple roles for user when admin update user roles

I have table as follow

user_roles

   username || role_name

   user1 | role1
   user2 | role2

users

   username || password

   user1 | abc

now suppose admin updates username to guest as well as assign multiple roles lets say 5 roles(role1,role2,role3,role4,role5) then how to update table ?

do I need to first delete existing records from both tables ?

share|improve this question
    
Since this is considered basic SQL, I am voting to have this moved to Stack Overflow. –  Max Vernon Nov 13 '13 at 16:01
add comment

closed as off-topic by Max Vernon, RolandoMySQLDBA, dezso, Mark Storey-Smith, billinkc Nov 14 '13 at 2:34

If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 1 down vote accepted

Typically you might use ID fields to uniquely identify each row in the Users, Roles, and Users_Roles tables. This allows much more flexibility for things like changing the name of a user, or a role.

I use SQL Server, so my example below shows syntax for that system; you should be able to adapt this for MySQL very easily.

USE tempdb;

CREATE TABLE Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserName varchar(255)
);

CREATE TABLE Roles
(
    RoleID INT NOT NULL CONSTRAINT PK_Roles PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , RoleName varchar(255)
);

CREATE TABLE UsersRoles
(
    UserID INT NOT NULL
    , RoleID INT NOT NULL
    , CONSTRAINT PK_UsersRoles PRIMARY KEY CLUSTERED (UserID, RoleID)
);

INSERT INTO Users (UserName) VALUES ('Max');

INSERT INTO Roles (RoleName) VALUES ('User');
INSERT INTO Roles (RoleName) VALUES ('SuperUser');

SELECT * FROM Users;
SELECT * FROM Roles;

INSERT INTO UsersRoles (UserID, RoleID) VALUES (1,1);
INSERT INTO UsersRoles (UserID, RoleID) VALUES (1,2);

SELECT U.UserName, R.RoleName
FROM dbo.Users U 
    INNER JOIN dbo.UsersRoles UR ON U.UserID = UR.UserID
    INNER JOIN dbo.Roles R ON UR.RoleID = R.RoleID
ORDER BY U.UserName, R.RoleID;

UPDATE dbo.Users 
SET UserName = 'Maxwell' 
WHERE UserID = 1;

UPDATE dbo.Roles 
SET RoleName = 'Standard User' 
WHERE RoleID = 1;

INSERT INTO dbo.Roles (RoleName) VALUES ('NewRole');

DELETE FROM dbo.UsersRoles
WHERE UserID = 1 AND RoleID = 2;

INSERT INTO dbo.UsersRoles (UserID, RoleID) VALUES (1,3);

SELECT U.UserName, R.RoleName
FROM dbo.Users U 
    INNER JOIN dbo.UsersRoles UR ON U.UserID = UR.UserID
    INNER JOIN dbo.Roles R ON UR.RoleID = R.RoleID
ORDER BY U.UserName, R.RoleID;

DROP TABLE dbo.Roles;
DROP TABLE dbo.Users;
DROP TABLE dbo.UsersRoles;

The results show changing the name of a user and role, as well as modifying role membership for a given user.

enter image description here

share|improve this answer
1  
thank you very much for ans its very helpful to clear basic concepts :) –  Sumit Nov 13 '13 at 17:01
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.