delete_from [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 thexRecord .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.

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
**************/
}

Happy DAXing
........