Data manipulation

  • 10 minutes

You can write code to manipulate data that is stored in the database.

  • Use a select method to declare the data to modify.
  • An insert method is used to add one record at a time to a table.
  • An update method is used to modify the current record in the appropriate system fields.
  • Use a where clause to specify a condition that the update method tests as it processes each row of the table.
  • A delete method is used to remove records from a table. Before using an update or delete method, you must use a select method to define the data to update or delete.
  • To select multiple records or fields, you initially use a select method and then use a next method to fetch the next record in a table.
  • To loop over many records that meet specific criteria, you can use a while select method.
  • To work on many records at the same time, use the record set-based insert_recordset, update_recordset, and delete_from statements. These statements complete the operation in a single database trip, which reduces communication between the application and the database and helps improve performance.

The select statement fetches or manipulates data from the database using X++. You can use the select statement to fetch one record or field. You can use other statements such as next to fetch more records, or use a while select to traverse multiple records.

For example, the following code sample fetches all columns in the first row of the CustTable, then it prints the value in the AccountNum column of that row:

CustTable custTable;
select firstonly custTable; //this is a short notation for 'select firstonly * from custTable;'  
info("AccountNum: " + custTable.AccountNum);

The following are code samples for insert, update, and delete methods. The samples use a fictitious MyCustomerTable table containing fictitious fields for AccountNumber and CustomerName.

  • insert method

    //This will insert a new record into a sample MyCustomerTable table. The only mandatory field that is set on this table is AccountNumber.

private void AddCustomer()
{
    MyCustomerTable myCustomerTable;
    ttsBegin;
         myCustomerTable.AccountNumber = "1234";
         //The new record will have the account number 1234.
         myCustomerTable.insert();
    ttsCommit;
}

  • update method

    //This will update the record where the AccountNumber field is set to 1234 by adding more information in the CustomerName field.

private void UpdateCustomer()
{
    MyCustomerTable myCustomerTable;
    ttsBegin;
        select forUpdate myCustomerTable
        where myCustomerTable.AccountNumber == "1234";
        //Now we will update the CustomerName field to be Sally
        myCustomerTable.CustomerName = "Sally";
        myCustomerTable.update();
    ttsCommit;
}

  • delete method

    //This will delete the record from the MyCustomerTable table where the AccountNumber field is set to 1234.

private void DeleteCustomer()
{
    MyCustomerTable myCustomerTable;
    ttsBegin;
        while select forUpdate myCustomerTable
        where myCustomerTable.AccountNumber == "1234"
        {
        myCustomerTable.delete();
        }
    ttsCommit;
}