UPDATE (SQL Server Compact)

Modifies existing data in a table in Microsoft SQL Server Compact.

UPDATE table_name 
   [ WITH ( < table_hint > ) ]
   { column_name = { expression | DEFAULT | NULL } } [ ,...n ] 
   [ WHERE < search_condition > ] 
   [ OPTION ( <query_hint> [ ,...n ] ) ]


The name of the table to update.

WITH (table_hint)

Specifies one or more table hints that are allowed for the target table. The WITH keyword and the parentheses are required. For the list of allowed table hints, see Table Hint (SQL Server Compact).


Specifies the list of column or variable names to be updated.


A column that contains the data to be changed. The column_name argument must reside in the specified table and should be specified only once in the SET clause.


A variable, literal value, or expression that returns a single value. The value returned by expression replaces the existing value in column_name.


Specifies that the default value defined for the column is to replace the existing value in the column. This can also be used to change the column to NULL if the column has no default and is defined to allow null values.


Specifies the conditions that limit the rows that are updated.

< search_condition >

Specifies the condition to be met for the rows to be updated. There is no limit to the number of predicates that can be included in a search condition.

OPTION ( <query_hint> [,…n] )

Specifies that query optimizer hints are used to customize the way SQL Server Compact processes the statement. For more information, see Query Hint (SQL Server Compact).

  • Identity columns cannot be updated.

  • If a WHERE clause is not specified, all rows of the table are updated.

  • The search condition in the WHERE clause is evaluated for each row of the table before updating any row of the table.

  • If an update to a row violates a constraint or rule, if it violates the NULL setting for the column, or if the new value is an incompatible data type, the statement is canceled, an error is returned, and no records are updated.

  • All nchar columns are right-padded to the defined length.

  • All trailing spaces are removed from data added to nvarchar columns, except in strings containing only spaces. These strings are truncated to an empty string.

The following example updates the Orders table by changing the shipping address for all orders made by a company with a customer ID of "VINET".

UPDATE Orders SET [Ship Address] = '21 rue de l''xylophie' WHERE [Customer ID] = 'VINET'