Delete Syntax

You can programmatically delete data from the Data Warehouse. A Data Deletion Data Transformation Services (DTS) task is provided for deleting all the data in the Data Warehouse, or for deleting Web log import data by using the TaskHistoryID property. For information about the Data Deletion DTS task, see Running the Data Deletion DTS Task.

The delete syntax format is:

   DELETE [FROM] BaseClassName
      [WITH (DELETEORPHANS | ONLYFACT | UPDATECHILDREN)] 
      [WHERE where_clause]

• BaseClassName
  The name of the class containing the data to delete.
• DELETEORPHANS
  Specifies to delete any orphans produced by the delete operation. An orphan is an instance in a parent class (for example, a dimension class such as Date) that is not referenced by any of its child classes (for example, a fact class such as Request). You can periodically delete with the DELETEORPHAN option, but in general it is not necessary.
• ONLYFACT
  Specifies to only delete children of BaseClassName that are marked as FACT tables.
• UPDATECHILDREN
  Specifies to update the children, if any, of BaseClassName. For example, if you are deleting a parent class that has children referencing it, UPDATECHILDREN updates the child references to the default row.

where_clause - memberpath [NOT] operator testvalue [AND where_clause]

memberpath - [BaseClassName.][pathelements]MemberName

**      pathelements** - (ParentName|RelationToParentName).[pathelements]

**            ParentName** - the name of the class that is a parent to the class

            **      **immediately preceding it in memberpath

**            RelationToParentName** - the name of the relation between the parent

         **         **and its base class

**      MemberName** - the name of a class member from the rightmost class in

         memberpath; this specifies the class member to test in where_clause

operator - one of the following operators:

testvalue - one of the following: value, IN valuelist, NULL

**      value** - the value to test MemberName against in where_clause

**      valuelist -** a comma-delimited list of values surrounded by parentheses

Delete Syntax Example

Set cmdDel = WScript.CreateObject("ADODB.Command.2.5")

cmdDel.CommandText = "DELETE FROM Request"
cmdDel.CommandText = "DELETE FROM Request
                         WHERE RequestNum > 2"
cmdDel.CommandText = "DELETE FROM Date
                         WHERE DTimestamp > '1998-10-29 00:00:00'"
cmdDel.CommandText = "DELETE FROM Date
                         WITH UPDATECHILDREN"
cmdDel.CommandText = "DELETE FROM Date
                         WITH DELETEORPHANS"
cmdDel.CommandText = "DELETE FROM Date
                         WITH UPDATECHILDREN
                         WHERE DTimestamp > '1998-10-29 00:00:00'"
cmdDel.CommandText = "DELETE FROM Date
                         WITH DELETEORPHANS
                         WHERE Date.DTimestamp > '1998-10-29 00:00:00'"
cmdDel.CommandText = "DELETE FROM Visit
                         WHERE DTimestamp > 1998-10-29 00:00:00'"
cmdDel.CommandText = "DELETE FROM Visit
                         WHERE DTimestamp
                            IN ('1999-10-29 00:00:00',
                                '1999-10-30 00:00:00')"
cmdDel.CommandText = "DELETE FROM Request
                         WHERE Request.Loguser.IPRef.Hostname = " & _
                           "'1.1.1.1'"
cmdDel.Execute

Copyright © 2005 Microsoft Corporation.
All rights reserved.