Sql Server系列：Update語句

libingql發表於2014-11-21
SQLServer

1 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}

2 更新整列

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

3 指定計算值

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

4 使用DEFAULT值更新

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

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

5 WHERE條件限制更新多列欄位

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

6 參考資料

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

相關文章