SQL Server series: UPDATE statement

The UPDATE statement is used to update the existing data with the update syntax:

[With <common_table_expression> [... n] ]UPDATE     [TOP (expression) [PERCENT]] {{Table_alias| <Object> |rowset_function_limited[With (<Table_Hint_Limited> [... n] ) ]      }      | @table_variable        }    SET{column_name={Expression| DEFAULT | NULL }          |{Udt_column_name. {{Property_name=expression|Field_name=expression}|Method_name (argument[,... N] )                              }          }          |column_name {. WRITE (expression,@Offset,@Length ) }          | @variable =expression| @variable = column =expression|column_name {+= | -= | *= | /= | %= | &= | ^= | |=} expression| @variable{+= | -= | *= | /= | %= | &= | ^= | |=} expression| @variable = column{+= | -= | *= | /= | %= | &= | ^= | |=} expression}[,... N]     [<output clause>]    [from{<table_source>} [,... n] ]     [WHERE {<search_condition> | {[Current of {{[GLOBAL]cursor_name}|Cursor_variable_name}] }            }     ]     [OPTION (<query_hint> [,... n] ) ][ ; ]<Object>::={     [server_name. database_name. schema_name. | database_name. [Schema_name] . |schema_name. ] Table_or_view_name}

1. Update the entire column

UPDATE [dbo]. [Product] SET [createdate] = GETDATE ()

2. Specify calculated values

UPDATE [dbo]. [Product] SET [unitprice] = [unitprice] * 2

3. Updating with default values

The default value for column CreateDate is set to GETDATE (), and the default value is null if not set.

UPDATE [dbo]. [Product] SET [createdate] = DEFAULT

4. Where condition limits update multi-column fields

UPDATE [dbo].[Product]SET [ProductName] = 'LINQ to SQL',[UnitPrice] =  -,[CreateDate] = GETDATE()WHERE [ProductID] = 1

5. References

Http://msdn.microsoft.com/zh-cn/library/ms177523.aspx

SQL Server series: UPDATE statement