UPDATE

Change existing data in a table.

Syntax
      [WITH common_table_expression [ ,...n ] ]
        UPDATE 
           [TOP (expression) [PERCENT] ] 
             {object | rowset_function_limited 
             [WITH ( Table_Hint_Limited [ ...n ] ) ] }
                SET  {column_expression_clause } [,...n] 
                   [OUTPUT Clause]
                      [FROM {table_source } [ ,...n ] ] 
                         [WHERE where_clause ] 
                            [OPTION ( query_hint [,...n] ) ] [;]

   column_expression_clause:
      column= { expression | DEFAULT | NULL }
      udt_column.property = expression 
      udt_column.field = expression } 
      udt_column.method ( argument [ ,...n ] ) 
      column { .WRITE ( expression , @Offset , @Length ) }
      @variable = expression 
      @variable = column = expression [,...n] 

   where_clause:
      search_condition 
      CURRENT OF  { {[GLOBAL] cursor} | cursor_variable }

   object:
     server.database.schema.table_or_view
     database.[schema].table_or_view
     schema.table_or_view

Key
   WITH common_table_expression
                  A temporary named result set or view
   TOP            The number or percent of random rows that will be updated.
   rowset_function_limited  Either the OPENQUERY or OPENROWSET function.
   SET            List of column or variable names to be updated.
   DEFAULT        Load the column's default value (if no default defined will set to NULL)
   udt_column     A user-defined type column.
   .WRITE         Update a substring of the column value: 
                  replace @Length units starting from @Offset of column with expression. 
   @variable      Set the variable to the value returned by expression.
   @variable = column = expression
                  Set the variable to the same value as the column. 
   @variable = column, column = expression, 
                  Set the variable to the pre-update value of the column.
   OUTPUT..       Return the updated rows.
   CURRENT OF     perform the update at the current position of the cursor.
   query_hint     Apply query optimizer hints

Examples

-- Apply a discount to orders placed today
UPDATE Sales.tblOrderLines
SET Linecost = Linecost * 0.95
FROM Sales.tblOrderLines AS sl
JOIN Sales.tblOrderHeader AS so
ON sl.OrderID = so.OrderID
AND so.OrderDate > CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112)) ;
GO

-- Wildcard update
UPDATE Sales.tblOrderLines
SET OrderDescription = REPLACE(OrderDescription, 'original string', 'replacement string')
WHERE OrderID = 64;
GO

-- Update with inner join
UPDATE t1
SET col = t2.col
FROM Table1 t1
INNER JOIN Table2 AS t2 ON t1.ID = t2.ID

"When debugging, novices insert corrective code; experts remove defective code" - Richard Pattis

Related commands:

INSERT
BULK INSERT
CREATE TABLE
DELETE
EXECUTE
IDENTITY (Property)
SELECT
SET ROWCOUNT
UPDATE(column) - Function, test for insert/update
Equivalent Oracle command: UPDATE


© Copyright SS64.com 1999-2014
Some rights reserved