Delete Syntax

This topic describes the syntax for programmatically deleting 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 the TaskHistoryID property. For information about the Data Deletion DTS task, see Deleting Data from the Data Warehouse.

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 child class that is not referenced by any of its parent classes.

ONLYFACT

Specifies to only delete children of BaseClassName that are marked as FACT tables.

UPDATECHILDREN

Specifies to update the children, if any, of BaseClassName.

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 preceeding 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:

= <> < > <= <= LIKE IS LIKE IS NULL IS IN NOT NULL
    != !>= !<= !> !< NOT LIKE IS NOT LIKE IS NOT NULL IS NOT IN NOT IN

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


All rights reserved.