SQL Iteration failing

Question

What I'm Trying To Achieve

We have a table within our database called pstats that focuses on reserving a person record for one user at a time. This table holds a record for each person and adds a user's UNIQUE ID and places it in lockedby field to reserve it, otherwise it's 0 when unlocked. This allows only the user to edit this record whilst open in their software, when the software window closes the software releases the record by setting the associated person record lockedby field to 0, thus allowing another user to edit it if they please.

The problem I appear to be having is that there is a report or procedure that keeps leaving records locked to one user and thus preventing others to access these person records. This is something I am working to resolve separately.

In meantime as the symptoms are quite obvious when you query the pstats table with :

select * From pstats where lockedby != 0

As one of the users ID is in like 30, 40, 50 person records lockedby field, for example :

pcode    |   lockedby
----------------------
100212        100025
100304        100025
100810        100025
100835        100025
100980        100025
117092        100025
117472        100025
117907        100025

The Code I Am Executing

To try and offer some relief to this i can manually set individual records by using :

Update sysDatabase.dbo.pstats set lockedby = 0 where pcode = 100212

As you can imagine this is rather time consuming when there are 30+ records to amend and takes time away from resolving the source of this problem. So i've tried to be a bit more clever and attempt a form of loop/list in the form of an IN statement, like below :

select * From pstats where lockedby != 0
Update sysDatabase.dbo.pstats set lockedby = 0 where pcode IN (100212,
100304,
100810,
100835,
100980,
117092,
117472,
117907)

The Error Message I Receive

Unfortunately this doesn't seem to work as well as i'd have hoped and i get the following error in response to running this :

(8 row(s) affected) Msg 512, Level 16, State 1, Procedure tr_pstats_Update, Line 4 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Would anybody have any suggestions how i might approach this better/differently?

Many Thanks

Answer 1

The error is self explanatory:

Msg 512, Level 16, State 1, Procedure tr_pstats_Update

You have a trigger on the table tr_pstats_Update that is designed to work only for one row update at a time and may look something like this:

CREATE TRIGGER tr_pstats_Update ON sysDatabase.dbo.pstats
AFTER UPDATE
AS
BEGIN
    DECLARE @id INT
    SET @id = (SELECT ID FROM DELETED)
END

And the error is caused by this line;

SET @id = (SELECT ID FROM DELETED)

DELETED will contain several rows because you are updating several rows in table.

One way to overcome this is rewrite trigger to joins on DELETED table. Another way is using cursor over this statement and update each pcode separately:

select * from (values(100212),
                     (100304),
                     (100810),
                     (100835),
                     (100980),
                     (117092),
                     (117472),
                     (117907)) t(pcode)

This will work if pcode column is unique in your table.