Overcomming Update Restriction in SQL

Question

Post reply

Overcomming Update Restriction in SQL

Peter Wilson-278151

SSC Veteran

Points: 276
More actions
January 13, 2006 at 9:30 pm

#92843

I have a view (vwUserActions) with two colums UserId and ActionId. The view can have many actions for a single user. I want to update an existing table keyed by Userld so that "Authorities" an nvarchar field has a 'Y' at string offset ActionId for each record in the first table. Otherwise, Authorities is initialized to all N's.
Declare @MaxActionId int
Declare @NoAuthorities nvarchar(256)
SET NOCOUNT ON;
Select @MaxActionId = Max(ActionId)
From dbo.tblAction;
Set @NoAuthorities = REPLICATE('N', @MaxActionId)
Update dbo.tblUser Set
Authorities = @NoAuthorities
Update dbo.tblUser Set
Authorities = STUFF( Authorities , UserActions.ActionId , 1 ,'Y' )
From dbo.vwUserActions UserActions
Where dbo.tblUser.UserId = UserActions.UserId
Of course this does not work as only the first (or indeterminate) row from vwUserActions for any given UserId is actually processed. How do I get SQL to process every record from vwUserAction?
Aside, I am compiling a set of authorities defined by roles, actions, user roles, role actions so that a single stored procedure call can
return user and authority data as output parameters for a .Net application.
Obviously, I could write a cursor to make this happen. However, I would like to know how to solve this problem with set based SQL.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

Here is an example that works. This is not a cursor, it is a loop with an update statement for each possible ActionID:

Declare

@MaxActionId int

Declare @NoAuthorities nvarchar(256)

declare @ActionID int

Select @MaxActionId = Max(ActionId)

From

dbo.tblAction;

Set

@NoAuthorities = REPLICATE('N', @MaxActionId)

Update

dbo.tblUser Set

Authorities

= @NoAuthorities

set

@ActionID = 1

while

@ActionID <= @MaxActionID begin

Update dbo.tblUser Set

Authorities

= STUFF( Authorities , UserActions.ActionId , 1 ,'Y' )

From dbo.vwUserActions UserActions

Where dbo.tblUser.UserId = UserActions.UserId

and UserActions.ActionID = @ActionID

set @ActionID = @ActionID + 1

end

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Peter Wilson-278151 SSC Veteran Points: 276 More actions · Answer 2

Kathi,

This is a big improvement over a cursor.

Although User/ActionId are relatively sparse and there are some missing numbers, this solution makes at most 256 updates irrespective of the number of users. This compares to a cursor which would make about 2000 updates given our current data.

Many thanks.