Creating an UPDATE loop?

[Jimdeb03]

I have a simple UPDATE statement against a table that unfortunately has an UPDATE trigger that forbids updating more than one 1 value at a time.

When running the UPDATE, this error is returned...
Server: Msg 512, Level 16, State 1, Procedure RSAS_TEST_UPR00400_UPDATE, Line 1
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.

The trigger is an encryped object and cannot be edited and is proprietary to another application. The UPDATE worked fine until the trigger was added to the table.

If the trigger cannot be edited or removed, my other option would be to edit my UPDATE into a loop so only one record is updated at a time.

This is the UPDATE statement that fails and returns the above error...

UPDATE upr99999	
SET PAYRTAMT = UBRTE.Curr_PAYRTAMT
FROM 
UPR004BASERATE UBRTE	
LEFT JOIN
upr99999 U999
ON (UBRTE.DR_ID0400 = U999.DEX_ROW_ID)

Any ideas how to UPDATE a single record at a time in this situation?

thanks
Jim

 

[RiverGuy]

SET ROWCOUNT 1. But you should put in a where clause so that you are only updating rows which have not been updated already.

 

[SQLSister]

This will be considerably slower. Why can't you change the trigger? Triggers should never be written to process only one record at a time and thus this one should be rewritten.

If this is a commercial product and you can't change the trigger without violating your warranty or some such, then first I would request the vendor fix the trigger. In the meantime, I would put the records you want to update in a select statement and run a cursor to so the import. I would only do this if the database is a commercial product that you cannot change.

If it is your own company's code, I would insist the trigger must be changed as it is broken (by definition any trigger which cannot handle multiple record inserts is broken).

"NOTHING is more important in a database than integrity." ESquared

 

[Jimdeb03]

Thanks for the advice but when I add SET ROWCOUNT 1 above the UPDATE statement, only one row is updated. But I get no error either

I need to update many rows not just one. My idea is to UPDATE one row at a time simply because the above mentioned trigger is returning an 'Subquery returned more than 1 value.... error.

How about putting my UPDATE statement inside a WHILE loop with a counter being the number of records to update.

 

[RiverGuy]

Yes, you put it in a loop. Pseudocode would be something like the following:

WHILE (SELECT COUNT(*) FROM Table WHERE NeedsUpdating > 0)
--Update a row
END

SQLSister is correct in that it will update slowly.

 

[SQLDenis]

good luck with that logic!

how will you know that you are not updating the same row over and over again?

are these primary keys?

UBRTE.DR_ID0400 U999.DEX_ROW_ID

if so then use a cursor, loop through the rowset and update one by one

it the keys are not unique then how will you know what you have updated? Do you have a lastupdated column on that table?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[RiverGuy]

That's why I mentioned in an earlier reply to add a where clause to track what needs to be updated. Maybe if there is an identity field, use a variable. Who knows without knowing his table structure.

 

[Jimdeb03]

The fields UBRTE.DR_ID0400 and U999.DEX_ROW_ID are primary and unique

Both tables are similar in structure and the UPDATE simply compares these Row ID's and UPDATES the PAYRTAMT field accordingly.

But we were able to circumvent the creation of a WHERE loop after solving the issue why the trigger was failing. It's a long story but the incorrect trigger was enabled for the table to UPDATE that was causing the error.

thanks for all your help