CODEWITHSUNDEEP
triggerssql-server-2008-r2sql-update
Jeremy McDevitt
Jeremy McDevitt

Reputation: 115

Update SQL rows one row at a time

I am trying to do a mass update on our SQL server which would easily be done by the following code in our situation.

update patient set security_level = '2'
where security_level = '1'

The problem is our SQL server connects to a mirth server that sends transactions to the State and the mirth server locks up if more than one row is updated at a time, so I'm hoping there is a way to update the rows one at a time. To prevent that our vendor who makes the software has several triggers in place. A piece of the code for the trigger is

IF (@numrows > 1) 
BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    SELECT @errmsg = OBJECT_NAME(@@PROCID) + ' : more than one row is updated in table Patient'                                 
    RAISERROR(@errmsg,16,21) 
    RETURN
END

If I were to disable the 3 triggers it would break other things. Thank you for any suggestions or thoughts you have.

Upvotes: 0

Views: 118

Answers (1)

GigiS
GigiS

Reputation: 66

declare @tmp_table table(
PRG int identity(1,1) Primary Key,
patient_id int
)

declare @start_value int = 1,
 @finish_value int,
 @patient_id int

Insert Into @tmp_table(patient_id ) Select patient_id  From patient where  security_level = '1'

Select @finish_value = max(PRG) From  @tmp_table

While @start_value <= @finish_value 
Begin
  --now get a key for patient and store in variables
  Select @patient_id =  patient_id 
  From  @tmp_table
  Where PRG = @start_value 

  --.. and now update by key
  Update patient
  set security_level = '2'
  Where patient_id  = @patient_id 


Set @start_value = @start_value  + 1
End

Upvotes: 1

Related Questions