Use the WHERE clause to implement UPDATE and DELETE statements as set-based operations, restricting the data involved with the operation to that which matches a specific criteria. By implementing these operations using this methodology, you only update or delete the rows you intend to update or delete, reduce the total amount of data affected by processing the operations, and reduce the number of row-level locks needed to process them.
These operations should be performed only by using server cursors if the criteria for determining the rows for the UPDATE or DELETE operations cannot be specified in the Transact-SQL statement itself.
The following two examples demonstrate the difference between a set-based update and a positional update using a cursor. Both of these examples update the YTD_Sales for each title covered by a specific PubID. The first example is a stored procedure used by Odbcapp.exe. It demonstrates the use of a default result set update that uses a WHERE clause.
/****** Object: Stored Procedure dbo.UpdtTitlesByPubID ******/
CREATE PROCEDURE UpdtTitlesByPubID @PubID char(4) AS
UPDATE TITLES SET YTD_SALES = YTD_SALES + (SELECT SUM(QTY) FROM
SALES WHERE TITLES.TITLE_ID = SALES.TITLE_ID)
WHERE PUB_ID = @PubID
GO
The preceding example is efficient and uses the server to perform the processing and row selection for the UPDATE.
The following example taken from Mybtrv32.exe demonstrates the inefficient use of a positional update through a cursor. This example must fetch through the cursor, updating each record that has the desired PubID. Notice the amount of fetching (round-trips between the client and the server) needed to process this request.
// The following code is taken from the GetTitlePublisher function in
// Mybtrv32.c
// Scroll through the cursor a row-at-a-time until the row needing
// updated is found.
while (!found)
{
memset( &tpRec, 0, sizeof(tpRec) ); // Initialize
// the client row buffer// Fetch the record
rc=SQLFetchScroll(hstmt8, FetchOrientation, FetchOffset);
if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))
{
if (rc!=SQL_NO_DATA_FOUND){
// Error encountered before end of cursor notify the user and return
ErrorDump("SQLFetchScroll TitlePub", SQL_NULL_HENV,
SQL_NULL_HDBC, hstmt8);
return B_UNRECOVERABLE_ERROR;
}
else {
return B_END_OF_FILE;} // End of cursor
// found. Record does not exist
} // Check to see if this is the record we want to update
if (!strcmp(keyBuffer, tpRec.PubID))
found=1;
}// The record to be updated has been found. The next step is to
// update it.
// The following code is taken from the CursorUPD function in
// Mybtrv32.c
// Initialize the client record buffermemset( &tpRec, 0, sizeof(tpRec) );
memcpy(&tpRec, dataBuffer, sizeof(tpRec));
// Initialize the tpRec data structure
memset( &tpRec, 0, sizeof(tpRec) );
memcpy(&tpRec, dataBuffer, sizeof(tpRec));/* Update the current row within the cursor. We rebind the columns
/* to update the length of the NULL terminated string columns. We
/* are using 0 for the numRows parameter to affect all rows in
/* the rowset. Since we have a rowset size of 1 only the positioned
/* row will be affected. The key value of the current record is not
/* changing so we issue the positioned update using SQLSet
/* Pos(SQL_UPDATE, SQL_LOCK_NO_CHANGE)*/ SQLBindCol(hstmtS, 1, SQL_C_CHAR, tpRec.TitleID, 7, &cbTitleID);
.
.
.
rc=SQLSetPos(hstmtS, numRows, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))
{
ErrorDump("SQLSetPos SQL_UPDATE for TITLEPUBLISHER FAILED", SQL_NULL_HENV, SQL_NULL_HDBC, hstmtS);
return B_UNRECOVERABLE_ERROR;
} return B_NO_ERROR;