Update Column

Function Description

The Update Column function allows you to 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 a cell is selected in the attribute table.

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.
Scope of Update: The scope of update provides two methods: Whole Field and Update Selected Records:
- Whole Field: Updates all field values in the specified field to be updated.
- Update Selected Records: Updates the selected records in the specified field to be updated, i.e., updates the values of all selected cells in the attribute table according to the specified rules.
Value Source: Specifies the source of values used to update the field values, including four sources: United Value Settings, Single-Field, Double-Field, and Function.

Value Source	Description
United Value Settings	Specifies a value; all cell values within the specified scope of update will be updated to this value.
Single-Field	Constructs a simple mathematical expression using a specified field (the field specified in the Update Column dialog box as the operation field) and a specified value (the value specified in the Update Column dialog box as the operation factor), e.g., SmID + 100, to update the cell values within the specified scope of update in the attribute table. For a record within the specified scope of update, the attribute value of the specified field (operation field) and the user-specified value (operation factor) are taken, along with the operation method, to construct an operation equation. The returned value is the new value of the cell to be updated.
Double-Field	Constructs a simple mathematical expression based on two specified fields (the first operation field and the second operation field specified in the Update Column dialog box) and the specified operation method, e.g., SmID + SmUserID, to update the cell values within the specified scope of update in the attribute table. For a record within the specified scope of update, the attribute values of the two specified fields (first and second operation fields) and the user-specified operation method are taken to construct an operation equation. The returned value is the new value of the cell to be updated.
Function	Constructs a function expression based on a specified field (the field specified in the Update Column dialog box as the operation field) according to the function rule specified by the user (operation function in the Update Column dialog box), e.g., Abs(SmID), to update the cell values within the specified scope of update in the attribute table. For a record within the specified scope of update, the attribute value of the specified field (operation field) and the user-specified operation function are taken to construct a function expression for calculation. The returned value is the new 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 expression operation.
Operation Field: When the value source is Single-Field or Function, the operation field specifies the field used to construct the mathematical expression or function expression.
First Operation Field, Second Operation Field: When the value source is Double-Field, these 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 operation rule between a single field and an operation factor or between two fields. It can be: Addition, Subtraction, Multiplication, Division, Modulus.

Operation Method	Description
+ (Addition)	For numeric parameters, adds the two values and returns a numeric result. For character parameters, concatenates the two strings, where the preceding parameter appears before the new character string. The + operation is the only operation between character parameters.
- (Subtraction)	Only applicable to numeric parameters: the value before the operator minus the value after the operator.
× (Multiplication)	Only applicable to numeric parameters: the value before the operator multiplied by the value after the operator.
/ (Division)	Only applicable to numeric parameters: the value before the operator 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.
% (Modulus)	Only applicable to numeric parameters: the value before the operator divided by the value after the operator, returning the remainder. 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 specifies the operation function.
- When the value source is Function, the two text boxes to the right of the Operation Function can be used to specify other parameters of the function. For details, see the Operation Function page.
- If the preset functions are insufficient, the user can click "More" in the drop-down list to edit a custom expression in the SQL Expression dialog box.

Calculation Expression: Displays and allows editing of the operational expression to be constructed. Click the button on the right side of the combo box to open the SQL Expression dialog box, where you can build a field expression, or directly enter a field expression in the Calculation Expression text box. For a record within the specified scope of update, the SQL Expression constructed by the user is calculated, and the returned value is the new value of the cell to be updated.

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

Data Source Type	Notes
UDB	When the update type is Boolean or Character, add the ifnull() function before the operation equation. For example, if the field to update is a character field char_1, and the single-field update operation factor is 'a', the default equation is char_1 \|\| 'a', which should be modified to ifnull(char_1, '') \|\| 'a'.
UDBX	Boolean and Character fields are handled the same as UDB: add the ifnull() function before the operation equation.
Oracle, OracleSpatial	When the update field is of type Long, modify BigInt to number(37) in the operation equation. For example, if the field to update is a Long field Int64_1, and the single-field update operation factor is 1, the default expression is nvl(Int64_1,0)+cast(1 as BigInt), which should be modified to nvl(Int64_1,0)+cast(1 as number(37)).
PostGIS, PostgreSQL	When writing the operational expression, note the requirements of PostgreSQL for numeric types and function parameter matching: ① When a field may be NULL, use COALESCE() to specify a default value. If the field value involved in the operation is NULL, the result will be NULL. Example: To increase the value of the Count field by a fixed value of 1, correct syntax: COALESCE(Count,0)+1. When Count is NULL, it is treated as 0 and then incremented by 1. When Count has a value, it is incremented by 1. ② Some numeric functions in PostgreSQL (e.g., ROUND(number, decimals)) only support numeric type parameters. To avoid execution errors due to type mismatch, use CAST(... AS numeric) for explicit type conversion. Example: To round a double-precision field Length to 2 decimal places and write the result back, correct syntax: ROUND(CAST(Length AS numeric), 2).
MySQL	① When performing a single-field update, add the ifnull() function before the operation equation. ② When the update field is an integer type, replace Integer with signed in the operation equation. For example, if the field to update is an integer field Int_1, and the single-field update operation factor is 1, the default expression is Int_1 + cast(1 as Integer), which should be modified to ifnull(Int_1,0) + cast(1 as signed). ③ When the update field is a floating-point type, write the floating-point field as cast(** as decimal(38,16)) in the operation equation. For example, if the field to update is a floating-point field Float_1, and the single-field update operation factor is 1.1, the default expression is Float_1+ cast(1.1 as Double), which should be modified to ifnull(Float_1,0) + cast(1.1 as decimal(38,16)). ④ When the update field is Character, Text, or Wide Char, string concatenation does not support \|\|. It should be modified to use the concat(field/value, value/field) function. For example, if the update field is a Text field Text_1, and during single-field update you want to concatenate the character 'aa', the correct 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. ③ Boolean fields only support the + and - operations; other operators are not supported.

Function Description

Function Entry

Parameter Description

Related Topics