Sql Server系列：Update語句

標籤：style   blog   http   io   ar   color   os   使用   sp   

　　UPDATE語句用來更新已有的資料，UPDATE文法：

[ 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 [dbo].[Product] SET [CreateDate] = GETDATE()

2. 指定計算值

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

3. 使用DEFAULT值更新

　　將列CreateDate的預設值設定為GETDATE()，若未設定則預設值為空白。

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

4. WHERE條件限制更新多欄欄位

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

5. 參考資料

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

Sql Server系列：Update語句