DELETE

Remove rows from a table.

Syntax
      [WITH common_table_expression [ ,...n ] ]
      DELETE 
      [TOP ( expression ) [ PERCENT ] ] 
      [FROM ] 
         server.database.schema.table/view
         database.[schema].table/view 
         schema.table/view 
         rowset_function_limited 
            [WITH ( table_hint_limited  [ ...n ] ) ]
                [OUTPUT_Clause] [;]
      FROM  table_source [ ,...n ] ] 
         [WHERE { search_condition 
         [ CURRENT OF 
               [ GLOBAL ] cursor_name 
               cursor_variable_name 
         ] 
           [OPTION ( Query Hint  [ ,...n ] ) ]  [;]

Key:

   TOP #   Number (or percent) of rows that will be deleted. 
           This will be a random subset of the rows

   rowset_function_limited
           Either the OPENROWSET or OPENQUERY  function,
           e.g. OPENQUERY(MyOracleSvr, 'SELECT name, id FROM scott.sales')

   FROM    An optional keyword

   rowset_function_limited  Either an OPENQUERY or OPENROWSET function
 
   OUTPUT_Clause  Return the deleted rows (or expressions)
                  Not supported when targeting a view or remote table.

   FROM table_source  An additional FROM clause - specify data from table_source
                      but delete the corresponding rows from the table in the first FROM clause.
                      This is an alternative to specifying a join, or a subquery in the WHERE clause.
                     (T-SQL extension)

   WHERE    Conditions to limit the number of rows to be deleted.
            If ommitted, DELETE will remove all rows from the table. 

   CURRENT OF 
            Perform the DELETE at the current position of the cursor.

   GLOBAL   The cursor_name refers to a global cursor.

DROP VIEW can be executed against indexed views.

Example

DELETE FROM MyDatabase.MyTable
WHERE Price > 500.00;
GO

# We always did feel the same, We just saw it from a different point of view# - Bob Dylan, Tangled Up In Blue

Related:

SELECT
UPDATE
Equivalent Oracle command: DELETE


© Copyright SS64.com 1999-2014
Some rights reserved