Update Column

Function Description

The Update Column feature allows you to quickly and uniformly modify the attribute values of a specified field in multiple or all records of the current attribute table based on certain conditions or rules, facilitating batch entry or changes to field attribute values.

This function is available when there are selected cells 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 update.
Scope of update: The scope of update provides two update methods: whole field and update selected records:
- Whole field: Updates all field values in the specified field to update.
- Update selected records: Updates the selected records in the specified field to update, meaning the values of all selected cells in the attribute table are updated according to the specified rules.
Value source: Used to specify the source of the value for updating the field, including four sources: united value settings, single-field, double-field, and function.

Value source	Description
United value settings	By specifying a value, all cells within the specified scope of update will be updated to this value.
Single-field	Builds a simple mathematical expression using a specified field (the operation field specified in the Update Column dialog) and a specified value (the operation factor specified in the dialog), such as SmID + 100, to update the values of cells within the specified scope of update in the attribute table. Within the specified scope of update, for the record where the cell to be updated is located, the attribute value of the specified field (operation field) and the user-specified value (operation factor) are retrieved, along with the operation method, to build an operation equation for calculation; the returned value is the updated value for the cell.
Double-field	Based on two specified fields (the first and second operation fields specified in the Update Column dialog) and the specified operation method, builds a simple mathematical expression for the fields, such as SmID + SmUserID, to update the values of cells within the specified scope of update in the attribute table. Within the specified scope of update, for the record where the cell to be updated is located, the attribute values of the two specified fields (first and second operation fields) and the user-specified operation method are retrieved to build an operation equation for calculation; the returned value is the updated value for the cell.
Function	Based on a specified field (the operation field specified in the Update Column dialog), builds a function expression for the field according to user-specified function rules (the operation function in the dialog), such as Abs(SmID), to update the values of cells within the specified scope of update in the attribute table. Within the specified scope of update, for the record where the cell to be updated is located, the attribute value of the specified field (operation field) and the user-specified operation function are retrieved to build a function expression for calculation; the returned value is the updated value for the cell.

Reverse: When the value source is single-field or double-field, checking this checkbox allows swapping the positions of the parameters on both sides of the operator in the expression before performing the operation.
Operation field: When the value source is Single-Field or Function, the operation field is used to specify the field for building the mathematical expression or function expression.
First operation field, Second operation field: When the value source is double-field, used to specify the two fields participating in building the operation expression.
Operation method: When the value source is Single-Field or Double-Field, the operation method is used to specify the operation rule between a single field and an operation factor or between two fields, which can be: add, subtract, multiply, divide, or modulo.

Operation method	Description
+ (Add)	For numerical parameters, it adds two numbers and returns a number; for character parameters, it concatenates two characters, with the preceding parameter coming before the new character; only the + operation is available for character parameters.
- (Subtract)	Only available for numerical parameters; subtracts the numerical value after the operator from the one before it.
× (Multiply)	Only available for numerical parameters; multiplies the numerical value before the operator by the one after it.
/ (Divide)	Only available for numerical parameters; divides the numerical value before the operator by the one after it; if the divisor is zero, the operation cannot be performed, and the dividend's value is returned.
% (Modulo)	Only available for numerical parameters; divides the numerical value before the operator by the one after it and returns the remainder; if the divisor is zero, the operation cannot be performed, and the dividend's value is returned.

Operation function: When the value source is function, used to specify 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 for the function. For details, refer to the Operation Function page.
- If the preset functions are insufficient, users can click the 'More' item in the drop-down list to edit a custom expression in the popped-up SQL Expression dialog.

Calculation expression: Used to display and edit the operation expression to be built. Click the button to the right of the combo box to pop up the SQL Expression dialog, where you can build the field expression or directly enter it in the calculation expression text box. Within the specified scope of update, for the record where the cell to be updated is located, the operation is performed based on the SQL Expression built by the user, and the returned value is the updated value for the cell.

Different database engines have different ways of writing the operation equations for single-field update column. Please refer to the following instructions:

Data source category	Notes
UDB	When updating types such as 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 during single-field update, the operation factor is a, the default operation 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 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 single-field update, the 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	① During single-field update, add the coalesce() function before the operation equation. ② When the field to update is a double field, change Double to numeric in the operation equation. For example, if the field to update is a double field Double_1, and during single-field update, the operation factor is 1, the default expression is Double_1 + cast(1 as Double), which should be modified to coalesce(Double_1, 0) + cast(1 as numeric). ③ Updating character fields is not supported.
MySQL	① During 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 single-field update, the 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 field to update 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 during single-field update, the 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 field to update is character, text, or wide char type, character concatenation in the operation equation does not support \|\|; it should be changed to the concat(field name/value, value/field name) function. For example, if the field to update is a text field Text_1, and during single-field update to concatenate the character 'aa', the correct operation expression is concat(ifnull(Text_1, ''), 'aa').
SQL Server	① During single-field update, add the ifnull() function before the operation equation. ② Updating character fields is not supported. ③ For Boolean fields, the operation methods only support + and -; other operators are not supported.

Function Description

Function Entry

Parameter description

Related Topics