delete_from

Article
05/18/2015

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

You can delete multiple records from a database table by using a delete_from statement. This can be more efficient and faster than deleting one record at a time by using the xRecord .delete method in a loop.

If you have overridden the delete method, the system interprets the delete_from statement into code that calls the delete method one time for each row that is deleted.

Example 1: Comparing Multi-record Delete Techniques

The code examples in this section show that some techniques for deleting multiple records are more efficient than other techniques.

Example 1a: Delete_From to Delete Multiple Records

The following X++ code example is an efficient way to delete multiple records.

    static void DeleteMultiRow1aJob(Args _args)
    {
        MyWidgetTable tabWidget;
        ;
        delete_from tabWidget
            where tabWidget .quantity <= 100;
    }

Example 1b: Delete Method Plus ForUpdate Keyword

The following X++ code example is inefficient. It issues a separate SQL delete call to the database server for each record. The xRecord .delete method never deletes more than one record per call.

    static void DeleteMultiRow1bJob(Args _args)
    {
        MyWidgetTable tabWidget; // extends xRecord.
        ;
        ttsBegin;
        while select
            forUpdate
            tabWidget
            where tabWidget .quantity <= 100
        {
            tabWidget .delete();
        }
        ttsCommit;
    }

Example 2: Delete Plus Inner Join Logic

X++ does not support an inner join on the delete_from statement. Therefore you cannot use the unmodified join keyword on the delete_from statement. However, there are other ways to logically accomplish an inner join.

The examples in this section show techniques for achieving inner join logic by a sequence of X++ statements.

Example 2a: Delete Method Plus Inefficient Inner Join Logic

The following X++ code example is inefficient. It issues a separate SQL delete call to the database server for each record.

    static void DeleteInnerJoin2aJob(Args _args)
    {
        MyWidgetTable tabWidget; // extends xRecord.
        ;
        ttsBegin;
        while select
            forUpdate
            tabWidget
            join tabGalaxy
                where
                    tabWidget .GalaxyRecId == tabGalaxy .RecId
                    && tabGalaxy .isTrusted == 0
        {
            tabWidget .delete();
        }
        ttsCommit;
    }

Example 2b: Delete_From Plus Efficient Inner Join Logic

The following X++ code example is relatively efficient. It issues a separate delete_from statement for each loop iteration. However, each delete_from statement can delete multiple records, a subset of all the records that the job deletes.

    static void DeleteInnerJoin2bJob(Args _args)
    {
        MyWidgetTable tabWidget; // extends xRecord.
        ;
        ttsBegin;
        while select
            from tabGalaxy
                where tabGalaxy .isTrusted == 0
        {
            delete_from tabWidget
                where tabWidget .GalaxyRecId ==
                      tabGalaxy .RecId;
        }
        ttsCommit;
    }

Example 3: Delete_From Notexists Join

You can use the notexists join keyword pair in a delete_from statement.

The delete_from statements in the following X++ code example are efficient. The notexists join clause enables the delete_from statement to delete a specific set of rows. In this example the delete_from statement removes all the parent order header rows for which there are no child order line rows.

Note

You can also use the exists join clause on the delete_from statement.

  
    static void DeleteFromNotexists3bJob(Args _args)
    {
        GmTabOrderHeader tabOHeader;
        GmTabOrderLine tabOLine;
        AddressState tabAddressState;
        str 127 sOH_Info;
        str 127 sOL_Data;
        int64 i64OHRecId;
        ;
        delete_from tabOLine;
        delete_from tabOHeader;
    
        // Inserts into parent table.
    
        sOH_Info = "Albert needs tires.";
        insert_recordset tabOHeader
            (OH_Info)
            select firstOnly sOH_Info from tabAddressState;
    
        sOH_Info = "Benson wants plastic.";
        insert_recordset tabOHeader
            (OH_Info)
            select firstOnly sOH_Info from tabAddressState;
    
        // Obtain a OrderHeader RecId,
        // use it to insert one child row.
    
        sOL_Data = "4 re-treads.";
        while select firstOnly tabOHeader
                order by OH_Info
                where tabOHeader .OH_Info like "A*"
        {
            i64OHRecId = tabOHeader .RecId;
            insert_recordset tabOLine
                (OL_Data ,OrderHeaderRecId)
                select firstOnly
                    sOL_Data ,i64OHRecId
                    from tabAddressState;
            break;
        }
    
        // Before the delete notexists.
        // Display all parent, and then all child rows.
    
        while select tabOHeader
            order by OH_Info
        {
            info(strFmt(
                "Before: OHeader:  OH_Info==%1 , RecId==%2"
                ,tabOHeader .OH_Info ,tabOHeader .RecId
                ));
        }
        while select tabOLine
            order by OL_Data
        {
            info(strFmt(
                "Before: OLine:  OL_Data==%1 , OrderHeaderRecId==%2"
                ,tabOLine .OL_Data ,tabOLine .OrderHeaderRecId
                ));
        }
    
        // Delete_From NotExists Join, to remove from the
        // parent table all order headers without children.
    
        delete_from tabOHeader
            notexists join tabOLine
                where tabOHeader .RecId ==
                    tabOLine .OrderHeaderRecId;
    
        info(strFmt
            ("%1 is the number of childless OHeader records deleted."
            ,tabOHeader.rowCount()));
    
        // After the delete notexists.
        // Display all parent, and then all child rows.
    
        info("- - - - - - - - - - - - - - -");
        while select tabOHeader
            order by OH_Info
        {
            info(strFmt(
                "After: OHeader:  OH_Info==%1 , RecId==%2"
                ,tabOHeader .OH_Info ,tabOHeader .RecId
                ));
        }
        while select tabOLine
            order by OL_Data
        {
            info(strFmt(
                "After: OLine:  OL_Data==%1 , OrderHeaderRecId==%2"
                ,tabOLine .OL_Data ,tabOLine .OrderHeaderRecId
                ));
        }
    /**************  Actual Infolog output
    Message (12:54:14 pm)
    Before: OHeader:  OH_Info==Albert needs tires. , RecId==5637144608
    Before: OHeader:  OH_Info==Benson wants plastic. , RecId==5637144609
    Before: OLine:  OL_Data==4 re-treads. , OrderHeaderRecId==5637144608
    1 is the number of childless OHeader records deleted.
    - - - - - - - - - - - - - - -
    After: OHeader:  OH_Info==Albert needs tires. , RecId==5637144608
    After: OLine:  OL_Data==4 re-treads. , OrderHeaderRecId==5637144608
    **************/
    }