Create an Update Query
About this task
Procedure
- In the main navigation menu, select
QUERIES.The QUERIES workspace appears.
- Select Add new query.The Create Query window appears.
- In the Query name box, enter a name, and then select Create. The name must contain at least one uppercase or lowercase letter.The available options for creating the query appear.
- In the Description box, enter a value.
- In the Query Type box, select Update.
- In the Entity box, select an entity from which you want to update results.The Conditions and Set Data sections appear.
- In the Conditions section, select Add, and then enter or select values as specified in the following table.
Field Description Entity Field Select one of the following values: - Required: Select this value if the field must always contain a value. For example, suppose you are creating a query to update account details based on the account number. If you select Required, when the query is run from an application, if an account number is not specified, then a message appears, stating that the field is required.
- Optional: Select this value if the field need not contain a value. For example, suppose you are creating a query to update account details based on the account number. If you select Optional, when the query is run from an application, if an account number is not specified, then all the fields will be updated. Therefore, use caution when selecting this value.Important: Exercise extreme caution while selecting Optional. If used incorrectly, it can corrupt the data.
Field Select the entity field based on which you want to create a condition. Operator Select the operator that you want to use to compare the value in the text box if Input Field is selected. Compare with Select one of the following values: - Input field: Select this value if you want to allow application users to specify a value that you want to compare with the entity field value. After you select this value, enter a name for the input field in the text box that appears. For example, suppose you want to create a query to update all devices in a specific site, and you want to allow the user to specify the site name. In this case:
- Select Input Type, and then enter Site Name.
- Modify the page in the application that contains the query to include the query to add an input control that allows the user to specify or select the Site Name field. Using the drag-and-drop method, map the Site Name field in the query with the site name control.
When the user enters or selects a value in the Site Name field in the application, and runs the query, all the devices with the selected site name are updated.
- Fixed Value: Select this value if you want to specify the value that you want to compare with the entity field value. After you select this value, enter a value in the text box that appears. For example, if you want to update the status of all devices if the temperature exceeds 40 degrees Celsius:
- In the Field box, select the field that stores temperature.
- In the Operator box, select >.
- Select Fixed Value, and then enter 40 in the text box.
- Query: Select this value to specify a query whose output you want to compare with the field values in the specified entity. After you select this value, a drop-down list box appears in which you can select a query. For example, if you want the query to update the status of devices when the temperature recorded by them exceeds a specified maximum temperature, you must create a query to get the maximum temperature, and select that query in this field.
- In the Set Data section, select Add, and then enter or select values as described in the following table.
Field Description Entity field Select the entity field whose values you want to update using the query. Value Select one of the following values: - Input field: Select this value if you want to update values specified by application users, and then enter a name in the Input Data Name box. Add an input control for the field in the application, and map it to the input field in the query.
- Fixed value: Select this value if you want to insert a fixed value, and then enter the value in the Name box.
Input Data Name This field appears only if you select Input field in the Value box. Enter the name of the field that you will add in the application. Application users can then provide the value by accessing the application. Name This field appears only if you select Fixed value in the Value box. Enter the value that you want to update using the query. - Select Save or Save And Exit.The query is created.