update_recordset

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

The X++ SQL statement update_recordset enables you to update multiple rows in a single trip to the server. This means that certain tasks may have improved performance by using the power of the SQL server.

update_recordset resembles delete_from in X++ and to UPDATE SET in SQL. It works on the database server-side on an SQL-style record set, instead of retrieving each record separately by fetching, changing, and updating.

If the update method is overridden, the implementation falls back to a classic looping construction, updating records one by one just as delete_from does for deletions. This also means that the construction works on temporary tables, and whole-table-cached tables by using the looping construction.

Example 1: Assigning from a Math Expression

This example updates the table myTableBuffer and increments the value in field1 by ten percent in all records in the table.

    MyTable myTableBuffer;
    ;
    update_recordset myTableBuffer
    setting field1 = field1 * 1.10;

Example 2: Using a Where Clause

This example updates the table myTable in all records where field1 has the value 0. field1 is assigned the new value 1; field2 is assigned the value of the sum of fieldX and fieldY.

This example updates multiple fields at the same time, and it updates only those rows that satisfy the where clause.

    MyTable myTableBuffer;
    ;
    update_recordset myTableBuffer
    setting
        field1 = 1,
        field2 = fieldX + fieldY
    where field1 == 0;

Example 3: Joining Tables in an Update

This example shows that the update_recordset statement supports the joining of several tables. Data from the joined tables can be used to assign values to fields in the table that is being updated.

    static void Join22aJob(Args _args)
    {
        TableEmployee tabEmpl;
        TableDepartment tabDept;
        TableProject tabProj;
        ;
        update_recordset tabEmpl
        setting
            currentStatusDescription = tabDept .DeptName
                + ", " + tabProj .ProjName
        join tabDept
            where tabDept .DeptId == tabEmpl .DeptId
        join tabProj
            where tabProj .ProjId == tabEmpl .ProjId;
    
        info(strFmt("Number of records updated is %1."
            ,tabEmpl .rowCount()));
    }

See also

Speeding Up SQL Operations

Maintain Fast SQL Operations

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.