delete_from

Article
08/03/2018

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