Update column

Function Description

Update column can be used to quickly and uniformly modify the attribute values of Specify Field of multiple records or all records in the current attribute table according to certain conditions or rules, so as to facilitate batch entry or change of attribute values of fields;

The function is available when there is a selected cell in the attribute table;

Function entrance

Property Sheet tab-> Edit-> Update Column button.
Toolbox-> Data Processing-> Vector-> Update Column.
Property Sheet window Context Menu-> Update Column button.

Parameter Description

To be updated Field: Click the Drop-down Button on the right to select the field to be updated.
Scope of update: Scope of update provides two update methods: Whole Field and Update Selected Records.
- Whole Field: Update all the field values in the specified Field to be updated;
- Update Selected Records: Update the selected records in the specified Field to be updated, that is, update the values of all selected cells in the attribute table according to the specified rules.
Value Source: Used to specify the value source for the Update Field value, including United Value Settings, Single-Field, Double-Field, and Function.

Source of values	Description
United Value Settings	By specifying a value, the values of the cells within the specified Scope of update are updated to that value.
Single-Field	Constructs a simple mathematical expression of the specified field (the field specified by the operand field in the Update Columns dialog box) and the specified value (the value specified by the operand in the Update Columns dialog box), for example, SmID + 100, to update the value of the cell within the Scope of update specified in the property sheet. Within the specified Scope of update, for the record of a cell to be updated, the attribute value of the specified field (operation field) and the value (operation factor) and operation mode specified by the user are taken out to construct an operation equation for operation, and the returned value is the updated value of the cell to be updated.
Double-Field	Based on the specified two fields (the fields specified by the first operation field and the second operation field in the update column dialog box) and the specified operation mode, a simple mathematical expression of the field is constructed, for example, SmID + SmUserID, To update the value of the cell within the Scope of update specified in the property sheet. Within the specified Scope of update, for the record of a cell to be updated, the attribute values of the two specified fields (the first and second operation fields) and the operation mode specified by the user are taken out to construct an operation equation for operation, and the returned value is the updated value of the cell to be updated.
Function	Based on the specified field (the field specified by the operation field in the update column dialog box), the function expression of the field is constructed according to the function rule specified by the user (the operation function in the update column dialog box), for example, Abs (SmID), to update the value of the cell in the Scope of update specified in the attribute table. Within the specified Scope of update, for the record of a cell to be updated, the attribute value of the specified field (operation field) and the operation function specified by the user are taken out to construct a function expression for operation, and the returned value is the updated value of the cell to be updated.

Reverse: When the value source is Single-Field or Double-Field, after this check box is checked, the parameter positions on both sides of the operator in the expression can be exchanged, and then the expression operation can be performed.
Value to be updated: The value entered will be assigned to the Field to be updated. Note: When the field to be updated is an existing enumeration value field, values can be assigned in two ways: enumeration value and enumeration description.
Operation field: When the numeric source is Single-Field or Function, the operation field is used to specify the field used to construct the mathematical expression or function expression.
First Operation Field, Second Operation Field: When the value source is Double-Field, it is used to specify the two fields involved in the construction of the operation expression.
Operation mode: When the value source is Single-Field or Double-Field, the operation mode is used to specify the operation rule between Single-Field and operation factor or between Double-Field, which can be: addition, subtraction, multiplication, division and modulus.

Operation mode	Description
+ (plus)	For numeric parameters, it is to add two numeric values and return a numeric value; for character parameters, it is to connect two characters, where the former parameter precedes the new character, and there is only + operation between character parameters.
- (minus)	Only numeric parameters have this operation. The numeric value before the operator subtracts the numeric value after the operator.
× (multiply)	Only numeric parameters have this operation. The numeric value before the operator is multiplied by the numeric value after the operator.
/ (divided)	This operation is only available for numeric parameters. The number preceding the operator is divided by the number following the operator. This operation is not allowed when the divisor is zero. The value of the dividend is returned.
% (modulo)	This operation is only available for numeric parameters. The numeric value before the operator is divided by the numeric value after the operator to return the remainder value. When the divisor is zero, the operation cannot be performed. Returns the value of the dividend.

Operation function: When the value source is Function, the function used to specify the operation.
- When the value source is Function, the two text boxes to the right of the operation function can be used to specify the Other Parameters information of the function. See the Operational function page for instructions.
- When the preset functions are not enough, the user can click more items in the drop-down list to edit the custom expression in the pop-up SQL Expression.

Operation Equation: is used to display and edit the operation expression to be constructed. Click The button on the right side of the combo box to pop up the SQL Expression dialog box, where you can construct a field expression or directly enter a field expression in the operation equation text box. See the SQL Statement Query page for a description of SQL Expression. Within the specified Scope of update, for the record of a cell to be updated, the operation is performed according to the SQL Expression constructed by the user, and the returned value is the updated value of the cell to be updated.

Different database engines write different operation equations when selecting Single-Field Update Column. Please refer to the following description:

Data source category	Precautions
UDB	When the update Type is Boolean or character, the ifnull () function needs to be added before the operation equation. For example, if the Field to be updated is char _ 1, and the operand is a when updating a single field, the default operation equation is char _ 1 \| \| 'a', which needs to be modified to ifnull (char _ 1,) \| \| 'a'.
UDBX	Boolean and character fields are processed in the same way as UDB, and the ifnull () function is added before the calculation equation.
Oracle、OracleSpatial	When Update Field is Long, BigInt in the calculation equation is modified to number (37). For example, if the Field to be updated is a Long field Int64 _ 1, and the operand is 1 when updating a single field, the default expression is nvl (Int64 _ 1,0) + cast (1 as BigInt). Change to nvl (Int64 _ 1,0) + cast (1 as number (37)).
PostGIS 、PostgreSQL	① When updating a single field, add the coalesce () function before the calculation equation. ② When Update Field is a Double field, Double needs to be changed to numeric in the operation equation. For example, when the Field to be updated is a Double field, Double _ 1, and a single field is updated, the operand is 1, and the default expression is Double _ 1 + cast (1 as Double). Change to coalesce (Double _ 1,0) + cast (1 as numeric). ③ Character fields are not supported for column update operations.
MySQL	① When updating a single field, add the ifnull () function before the calculation equation. ② When Update Field is integer type, Integer needs to be modified to signed in the operation equation. For example, when the Field to be updated is Int _ 1, the operand is 1 and the default expression is Int _ 1 + cast (1 as Integer), which needs to be modified to ifnull (Int _ 1,0) + cast (1 as signed). ③ When Update Field is floating-point type, the floating-point field in the operation equation needs to be written as cast (* * as decimal (38,16)). For example, when the Field to be updated is a floating-point field Float _ 1 and a single-field field is updated, the operand is 1.1 and the default expression is Float _ 1 + cast (1.1 as Double). To be changed to ifnull (Float _ 1,0) + cast (1.1 as decimal (38,16)). ④ When Update Field is character type, text type or Wide Char, the character concatenation of the operation equation does not support \| \| and should be modified to concat (field name/value, value/field name) function. For example, when the Update Field is the text field Text _ 1, and the character AA is concatenated when the single-field field is updated, the correct writing method of the operation expression is: concat (ifnull (Text _ 1, ''), 'AA').
SQL Server	① When updating a single field, add the ifnull () function before the calculation equation. ② Character fields are not supported to perform column update operations. ③ For Boolean field, only + and-are supported, and other operator operations are not supported.

Function Description

Function entrance

Parameter Description

Related topics