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 am still very new to sql. I am having troubles updating one table. I am sending information from a web application into Microsoft SQL 2008 database. I have a table called EmployeePermissions that has employee ID and Permission ID. The Permission ID links this table to the Permissions table where different permissions are located and given a true or false value. If I am sending SQL the employee ID and the true or false values of the specific permissions, how do I update the Permissions table by sending the information through the EmployeePermissions table? Hopefully the diagram below can help explain a little better. Thanks!enter image description here

share|improve this question

1 Answer 1

up vote 0 down vote accepted

OK, so I'm not going to comment on the table design as I assume that this is just part of the tables.

To do the update that you are looking for there's a couple of options.

You can get the Permission ID and put it into a variable, then use that against the permission table.

DECLARE @PermissionId INT

SELECT @PermissionId = PermissionId
from Employee
where EmployeeId = 2

UPDATE Permission
SET Read='T', Write='T', Delete='T'
where Permission = @PermissionId

Or you can do it all with a join in your update statement.

UPDATE p
SET Read='T', Write='T', Delete='T'
FROM Permission p
JOIN Employee e ON p.PermissionId = e.PermissionId
WHERE e.EmployeeId = 2

In either case you'll want to properly parameterize the query in your application layer to prevent against SQL Injection.

share|improve this answer
    
♦ Thank you! I went with the second option and it worked great. Thanks for the quick response! –  Guy Hendrickson Jul 8 at 18:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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