Updating multiple columns The following example updates the values in the Bonus, CommissionPct, and SalesQuota columns for all rows in the SalesPerson table. The statement updates the value in the Color column of the Production. Product table for all rows that have an existing value of 'Red' in the Color column and have a value in the Name column that starts with 'Road'.
The following example updates the VacationHours column by 25 percent for 10 random rows in the Employee table. The following example updates the vacation hours of the 10 employees with the earliest hire dates. The common table expression returns a hierarchical list of parts that are used directly to build ProductAssemblyID and parts that are used to build those components, and so on.
Only the rows returned by the common table expression are modified. Other tables participating in the cursor are not affected. The example doubles the value in the ListPrice column for all rows in the Product table.
Specifying a compound operator The following example uses the variable NewPrice to increment the price of all red bicycles by taking the current price and adding 10 to it. Specifying a subquery in the SET clause The following example uses a subquery in the SET clause to determine the value that is used to update the column.
The subquery must return only a scalar value that is, a single value per row. Specifying a view as the target object The following example updates rows in a table by specifying a view as the target object. The view definition references multiple tables, however, the UPDATE statement succeeds because it references columns from only one of the underlying tables.
For more information, see Modify Data Through a View. Specifying a table alias as the target object The follow example updates rows in the table Production. Specifying a table variable as the target object The following example updates rows in a table variable. Employee; -- Update columns in the table variable. BusinessEntityID ; GO The previous example assumes that only one sale is recorded for a specified salesperson on a specific date and that updates are current. If more than one sale for a specified salesperson can be recorded on the same day, the example shown does not work correctly.
The example runs without error, but each SalesYTD value is updated with only one sale, regardless of how many sales actually occurred on that day. In the situation in which more than one sale for a specified salesperson can occur on the same day, all the sales for each sales person must be aggregated together within the UPDATE statement, as shown in the following example: SalesPersonID ; GO Updating Rows in a Remote Table Examples in this section demonstrate how to update rows in a remote target table by using a linked server or a rowset function to reference the remote table.
Updating data in a remote table by using a linked server The following example updates a table on a remote server. The linked server name, MyLinkServer, is then specified as part of the four-part object name in the form server. Note that you must specify a valid server name for datasrc.
The linked server name created in the previous example is used in this example. For more information, see ad hoc distributed queries Server Configuration Option. The word components is replaced with the word features by specifying the replacement word, the starting location offset of the word to be replaced in the existing data, and the number of characters to be replaced length.
This data is then replaced with the correct data by using the. The additional examples append data to the end of the column value, remove truncate data from the column and, finally, remove partial data from the column.
This example assumes that a file named Tires. We do not recommend this method for streaming large amounts of data to a file. Use the appropriate Win32 interfaces.
The following example replaces any text in the file record with the text Xray 1. Three methods are demonstrated. Using a system data type You can update a UDT by supplying a value in a SQL Server system data type, as long as the user-defined type supports implicit or explicit conversion from that type.
The following example shows how to update a value in a column of user-defined type Point, by explicitly converting from a string. Invoking a method You can update a UDT by invoking a method, marked as a mutator, of the user-defined type, to perform the update. The following example invokes a mutator method of type Point named SetXY. This updates the state of the instance of the type. Modifying the value of a property or data member You can update a UDT by modifying the value of a registered property or public data member of the user-defined type.
The expression supplying the value must be implicitly convertible to the type of the property. The following example modifies the value of property X of user-defined type Point.
Caution Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators. This hint specifies that a shared lock is taken on the table Production.
This hint instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.
VacationHours column and the updated value in the inserted. VacationHours column to the MyTableVar table variable. The procedure takes one input parameter, NewHours and one output parameter RowCount. The RowCount output parameter is used to return the number of rows affected to a local variable. The following example doubles the value in the ListPrice column for all rows in the Product table. Imagine you had to update this table: You can use a combination of a CTAS and an implicit join to replace this code: