VERIFIED SOLUTION i
X

How to use the Update Column Function in MapInfo Pro

UPDATED: March 22, 2017


MapInfo Pro allows user's to update a designated field in an editable table or query with either a selected string of data or with data from a 2nd table, based on join or relationship between the 2 tables.
Users can update a field ("my_rowid") in an editable table with a sequence of numbers with an update like this, that uses MapInfo Pro's "rowid" statement to update the USA table:
User-added image

User-added image
:
To update a field with a static string of information, users can update like this:
(note: to update character data, the string must be wrapped in quotes)

User-added image

User-added image

To update the USA table with a join to the USCTY153 point table, to update each state record with the count of USCTY153 points within, where the join is a "geographic" join based on the spatial relationship between the 2 tables, run the update like this:
Note that the geographic join is where the object from the point table "is within" the region object from the region table.
Points are "within" Regions, while Regions "contain" Points

User-added image

User-added image

To update the USA table with the count of the USCTY153 point objects within each state based on a "columnar" join, whereby a join can be established using a field in each table - in this example, join using the "State" field in each table:

User-added image

User-added image

The Update column function is extremely powerful tool used frequently in MapInfo Pro
Here is more detailed information about the dialog options:.
 
Update Column Dialog Box
Update Column allows you to change a column's value by updating a table based on its own data values or by updating a table based on data from another table. This dialog box remembers the column that was updated the last time update was run and the expression that was used for updating a column the last time an update was run.
Table to Update
Use this drop-down list to specify the table you want to update or where MapInfo Pro should create the temporary column.
Column to Update
Choose the column to update if the Table to update is different than the table selected in Get Value From Table. Add temporary column displays in the list.
Add new temporary column
Choose Add new temporary column if you want to add a temporary column to the update table. This is where your results are stored.
Get Value From Table
This option is automatically set to the same table as Table to update. A different table can be chosen from which MapInfo Pro will retrieve the update information.
Value
Displays when working with only one table. Type an expression into the Value box or use the Assist button to access the Expression dialog box to create an expression.
Join button
Displays the Join dialog box. The Join button is active when the data table is different than the table to update. When updating a column using data from another table, use the Join dialog box to specify how records in the two tables are matched.
Calculate
When selecting the Join button the Calculate field displays. The table from which the data is being retrieved  dictates the choice of options.
  • When Updating one table based on its own data values, MapInfo Pro automatically chooses the value option.
  • When updating one table based on data from another table, specify ValueMin, Average, Sum, Max, Count, Proportion Sum, WtAvg, Proportion Avg, or Proportion WtAvg.
Choose one of the following aggregate functions:
Count: Counts the number of records in a group. Takes * as its argument because it applies to the record as a whole and not to any particular field in a record.
Sum(expression): Calculates the sum of the values in expression for all the records in a group.
Average(expression): Calculates the average of the values in expression for all the records in a group.
Max(expression): Finds the highest value in expression for all records in a group.
Min(expression): Finds the lowest value in expression for all records in a group.
WtAvg: With weighted averaging, MapInfo Pro adjusts the calculation of averages so that the values from each selected object are weighted more or less heavily.
Proportion Sum: Aggregates data into a polygon. Accounts for the area of the polygon that overlaps the polygon receiving the aggregation. For example, if a third of an object's area falls within a polygon the proportion sum aggregate will put one third of the overlapping object's data value in the polygon.
Proportion Avg: Computes the average based on the proportion of values from the covered areas (weighs the averages according to area). For example, if 80% of a new object's area is from Object A having a mortality rate of .8% and 20% of the new object's area is from Object B having a birth mortality rate of .65%, then the birth rate of the object would be equal to .8 x .008 + .0065 x .2 = .0077 or .77%
Proportion WtAvg: Computes the average based on the proportion of values from the covered areas (weighs the averages according to another field whose value is proportioned). For example, (continuing from the example above), Population of the new object, Object A = 34,000. Population of new object, Object B = 26,000. The birth mortality rate is (.8 x 34,000 + .6 x 26,000) /6,000 = .713.
Of: Specify values stored in a single column or a mathematical expression based on values in one or more tables. Specify the field or build your own expression by choosing Expression from the drop-down list and using the Expression dialog box. expressions or fields are evaluated according to the selected Aggregate Function or Value. Value is automatically selected when updating one table based on its own data values.
Browse Results
Check the Browse results check box to display an updated table. Clear the box to have no results displayed
Assist button
The Assist button will only display when working with two tables. To create an expression, click on the Assist button, the Expression dialog box displays.
Clear button
Clears the expression value edit box.
 







 

Environment Details


 

Downloads

  • No Downloads