OQL Statements

Introduction

From Mendix version 11.1, you can delete objects in bulk using OQL DELETE statements.

From Mendix version 11.3, you can also update object attributes in bulk using OQL UPDATE statements.

OQL statements are translated to SQL statements that are sent to the database. This can be much faster than retrieving the objects in a microflow and then updating or deleting the resulting list.

This feature is experimental and currently only accessible through the Java API by writing a Java action.

Java API for OQL updates

OQL Statements can be executed using the Core.createOqlStatement Java API. For example:

Core.createOqlStatement("DELETE FROM Module.Customer WHERE Name = 'Mary'").execute(context) 

You can pass values as parameters to the query. For example:

Core.createOqlStatement("DELETE FROM Module.Customer WHERE Name = $nameParam")
    .setVariable("nameParam", customerName)
    .execute(context)

The execute() method returns the number of objects that were affected by the statement.

DELETE Statement

The syntax of DELETE statements is:

DELETE FROM <entity> WHERE <condition>

condition can be anything that can appear in an OQL WHERE clause.

OQL DELETE Limitations

• You cannot use OQL DELETE with entities that have associations with non-default delete behavior. These are associations that use either "Delete as well" or "Delete only if not associated".
• You cannot use OQL DELETE to delete objects of type System.FileDocument or any specialization of it.
• The general limitations for OQL statements also apply. See General Limitations for OQL Statements, below.

UPDATE Statement

The syntax of UPDATE statements is:

UPDATE <entity>
SET { <attribute> = <expression> } [ ,...n ]
WHERE <condition>

entity is the entity whose objects are being updated.

attribute is an attribute of the entity that is being updated. Multiple attributes can be updated in the same statement.

expression is a new value of an attribute. Any OQL expression is allowed. The value type of the expression should match the attribute type according to type coercion precedence.

condition can be anything that can appear in an OQL WHERE clause.

Example:

UPDATE
    Module.Customer
SET
    TotalAmount = (
        SELECT SUM(Amount)
        FROM Module.Order
        WHERE Module.Order_Customer/Module.Customer/ID = Module.Customer/ID
    ),
    Location = Module.Customer_Address/Module.Address/City,
    Name = UPPER(Name)

OQL UPDATE Limitations

• At the moment, it is only possible to update attributes, not associations.
• If a subquery or a long path over a many-to-one or many-to-many association is used as expression, it can result in multiple values. In that case, a database-level exception will occur when running the statement.
• In the case of inheritance, it is not possible to simultaneously update an attribute and use that attribute in an expression to update an attribute on another inheritance level. See the example in Mixed Attribute Update, below.
• The general limitations for OQL statements also apply. See General Limitations for OQL Statements, below.

Example of Mixed Attribute Update

To clarify the limitation on simultaneous update of different levels of inheritance, let's use the following model as an example.

An entity SuperEntity with an integer attribute GeneralizationAttribute has a specialization entity SubEntity with an integer attribute SpecializationAttribute. They are both in module Module.

The following statement will pass. It uses an attribute on one level of inheritance to update the attribute on the other level, which is allowed as long as that attribute is not being updated too.

UPDATE
    Module.SubEntity
SET
    GeneralizationAttribute = SpecializationAttribute

The following statement will fail. This time, the attribute that is used to update the attribute. on another inheritance level is being updated itself.

UPDATE
    Module.SubEntity
SET
    GeneralizationAttribute = SpecializationAttribute,
    SpecializationAttribute = 1

Joins

You cannot directly join other entities in the FROM clause of OQL DELETE or in the UPDATE clause of OQL UPDATE. However, you can achieve the same result using long paths or subqueries. For example:

DELETE FROM Module.Order
WHERE Module.Order_Customer/Module.Customer/Name = 'Mary'

or

UPDATE Module.Order
SET CustomerName = 'Mary'
WHERE ID IN (
        SELECT ID
        FROM Module.Order
        INNER JOIN Module.Customer ON Module.Customer/CustomerID = Module.Order/CustomerID
        WHERE Module.Customer/Name = 'Mary' )

General Limitations for OQL Statements

• OQL statements can be used only with persistable entities.
• Entity access rules are not applied to any OQL statements.
• No event handlers will be executed.
• Runtime and client state will not be updated with the changes.