Update Column

Feature Description

The Update Column function can quickly modify the attribute values of a specified field for multiple records or all records in the current attribute table according to certain conditions or rules, facilitating batch entry or modification of field attribute values.

This function is available when there are selected cells in the attribute table.

The Update Column window opens in a modeless manner. After the window opens, you can continue to operate on the attribute table and the map. Each time an update is executed, the system processes the data according to the currently set update scope. After the update is completed, the attribute table automatically refreshes and displays the update results. You can reselect records and execute the update again without closing the Update Column window.

Function Entry

Table tab -> Edit -> Update Column button.
Toolbox -> Data Processing -> Vector -> Update Column.
Table window context menu -> Update Column button.

Parameter Description

Field to Update: Click the drop-down button on the right to select the field to be updated.
Update Scope: The update scope provides two update methods: Entire Field and Update Selected Records.
- Entire Field: Indicates updating all field values in the specified field to update.
- Update Selected Records: Indicates updating the selected records in the specified field to update, i.e., updating the values of all selected cells in the attribute table according to the specified rules. You can reselect records in the attribute table and execute the update again while the Update Column window remains open.
Value Source: Used to specify the source of values for updating the field values, including Constant Value, Single Field, Double Field, and Function.

Value Source	Description
Constant Value	By specifying a value, the values of cells within the specified update scope will all be updated to this value.
Single Field	Constructs a simple mathematical expression between a specified field (the field specified by the operation field in the Update Column dialog) and a specified value (the value specified by the operation factor in the Update Column dialog), for example: SmID + 100, to update the values of cells within the specified update scope in the attribute table. Within the specified update scope, for the record containing a cell to be updated, the attribute value of the specified field (operation field) and the user-specified value (operation factor) and operation method are retrieved to construct an operation equation for calculation. The returned value is the updated value of the cell to be updated.
Double Field	Based on two specified fields (the fields specified by the first operation field and the second operation field in the Update Column dialog) and the specified operation method, constructs a simple mathematical expression for the fields, for example: SmID + SmUserID, to update the values of cells within the specified update scope in the attribute table. Within the specified update scope, for the record containing a cell to be updated, the attribute values of the two specified fields (first and second operation fields) and the user-specified operation method are retrieved to construct an operation equation for calculation. The returned value is the updated value of the cell to be updated.
Function	Based on a specified field (the field specified by the operation field in the Update Column dialog), constructs a function expression for the field according to the user-specified function rule (the operation function in the Update Column dialog), for example: Abs(SmID), to update the values of cells within the specified update scope in the attribute table. Within the specified update scope, for the record containing a cell to be updated, the attribute value of the specified field (operation field) and the user-specified operation function are retrieved to construct a function expression for calculation. The returned value is the updated value of the cell to be updated.

Reverse: When the value source is Single Field or Double Field, checking this checkbox swaps the positions of the parameters on both sides of the operator in the expression before performing the calculation.
Operation Field: When the value source is Single Field or Function, the operation field is used to specify the field for constructing the mathematical expression or function expression.
First Operation Field, Second Operation Field: When the value source is Double Field, these are used to specify the two fields involved in constructing the operational expression.
Operation Method: When the value source is Single Field or Double Field, the operation method specifies the arithmetic rule between the single field and the operation factor or between the two fields. It can be: Add, Subtract, Multiply, Divide, Modulo.

Operation Method	Description
+ (Add)	For numeric parameters, this adds two numeric values and returns a numeric value; for character parameters, this concatenates two characters, where the preceding parameter is placed before the new character. Only the + operation is available between character parameters.
- (Subtract)	Only numeric parameters support this operation method. The value before the operator is subtracted by the value after the operator.
× (Multiply)	Only numeric parameters support this operation method. The value before the operator is multiplied by the value after the operator.
/ (Divide)	Only numeric parameters support this operation method. The value before the operator is divided by the value after the operator. If the divisor is zero, the operation cannot be performed, and the value of the dividend is returned.
% (Modulo)	Only numeric parameters support this operation method. The value before the operator is divided by the value after the operator, and the remainder is returned. If the divisor is zero, the operation cannot be performed, and the value of the dividend is returned.

Operation Function: When the value source is Function, this is used to specify the function for the operation.
- When the value source is Function, the two text boxes to the right of the operation function can be used to specify additional parameter information for the function. For specific details, please refer to the Operation Functions page.
- When the preset functions are insufficient, users can click the "More" item in the drop-down list and edit a custom expression in the pop-up SQL Expression dialog.

Calculation Expression: Used to display and edit the operational expression to be constructed. Click the button to the right of the combo box to pop up the SQL Expression dialog, where you can construct a field expression, or directly enter a field expression in the calculation expression text box. Within the specified update scope, for the record containing a cell to be updated, the calculation is performed according to the user-constructed SQL Expression, and the returned value is the updated value of the cell to be updated.

Different database engines have different syntax requirements for writing the operation equation for Single Field Update Column. Please refer to the following instructions:

Data Source Type	Notes
UDB	When the update type is Boolean or Character, the ifnull() function must be added before the operation equation. For example, if the field to update is a character field char_1, and during a single field update the operation factor is 'a', the default operation equation would be char_1 \|\| 'a', which needs to be modified to ifnull( char_1,'') \|\| 'a'.
UDBX	Boolean and Character fields are handled the same way as UDB, requiring the ifnull() function to be added before the operation equation.
Oracle, OracleSpatial	When the field to update is of type Long, change BigInt to number(37) in the operation equation. For example, if the field to update is a long field Int64_1, and during a single field update the operation factor is 1, the default expression would be nvl(Int64_1,0)+cast(1 as BigInt ), which needs to be modified to nvl(Int64_1,0)+cast(1 as number(37) ).
PostGIS, PostgreSQL	When writing the operational expression, pay attention to PostgreSQL's matching requirements for numeric types and function parameters: ① If a field may be NULL, use COALESCE() to specify a default value. This is because if the value of a field involved in the operation is NULL, the result of the operation will be NULL. Example: To increase the value of the field Count by a fixed value of 1, the correct syntax is: COALESCE(Count,0)+1. When the value of Count is NULL, it is treated as 0, and then 1 is added; when Count has a value, 1 is added to the original value. ② Some numeric functions in PostgreSQL (such as ROUND(number, decimals)) only support numeric type parameters. To avoid execution errors caused by numeric type mismatches, it is recommended to use CAST(... AS numeric) for explicit type conversion. Example: To round the value of a double-precision field Length to 2 decimal places and write it back to the original field, the correct syntax is: ROUND(CAST(Length AS numeric), 2)
MySQL	① When performing a single field update, add the ifnull() function before the operation equation. ② When the field to update is an integer type, change Integer to signed in the operation equation. For example, if the field to update is an integer field Int_1, and during a single field update the operation factor is 1, the default expression would be Int_1 + cast(1 as Integer), which needs to be modified to ifnull(Int_1,0) + cast(1 as signed). ③ When the field to update is a floating-point type, the floating-point field in the operation equation needs to be written as cast(** as decimal(38,16)). For example, if the field to update is a floating-point field Float_1, and during a single field update the operation factor is 1.1, the default expression would be Float_1 + cast(1.1 as Double), which needs to be modified to ifnull(Float_1,0) + cast(1.1 as decimal(38,16)). ④ When the field to update is a Character, Text, or Wide Char type, the \|\| operator is not supported for character concatenation in the operation equation. It should be modified to use the concat(field name/value, value/field name) function. For example, if the field to update is a text field Text_1, and during a single field update you want to concatenate the characters 'aa', the correct syntax for the operational expression is: concat(ifnull(Text_1,''), 'aa').
SQL Server	① When performing a single field update, add the ifnull() function before the operation equation. ② Character fields do not support the Update Column operation. ③ For Boolean fields, only the + and - operations are supported; other operators are not supported.

Feature Description

Function Entry

Parameter Description

Related Topics