Updating multiple rows without cursor
I want to update values in one table with values from another table.I have an update trigger on the table I'm updating and when I run my update I get the "Subquery returned more than 1 value." message referencing the trigger. In our company, bought-out items are supposed to have Traceable Type = 'T', except if they have Item Class = 'C2'.There can be a situation where you have to use a cursor, even though the experts say not to use cursors or to avoid them as much as possible.But if you look closely, most of the time we use cursors to iterate through a row collection and update the same table.I figure I can put the WHILE loop in a UDF and call the UDF in the SELECT clause.My plan was to raise an error when a circular reference was found but its saying 'Invalid use of 'RAISEERROR' within a function.'.
The easiest way to declare a cursor that can be used to update records is to simply use the default cursor type: Using the code above will declare a cursor which can used to update records.
I have highlighted the areas which is differ from compared with a normal cursor.
So far in this series we've been concerned with simply reading data from tables using a cursor.
The dep_id column of employee table will hold values from the id table of the department table, with a one too many relations between the department and employee tables.
Remember this is not a perfectly normalized data table as we just want some data to execute example queries on.
Search for updating multiple rows without cursor:
Relational database management systems including SQL Server are very good at processing data in sets.