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