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:
= | <> | < | > | <= | <= | 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
Copyright © 2005 Microsoft Corporation.
All rights reserved.