|Be the Query Ninja: Using the Update Column command|
|One important operation in maintaining tables is the ability to update the data contained in the table. MapInfo Pro's Update Column feature allows you to:|
Note that this article uses the 64 bit version of MapInfo Pro. The same capabilities are in the 32 bit interface. The Update Column command can be found in the Table menu.
- Add a temporary column or update an existing column with data from another table
- Update a table
- Place graphic information into visible columns
Using the Update Column command
There are two way to use Update Column. The first is through the TABLE tab, in the Edit group.
The second is through Create Thematic Maps.
In this example we will total the amount of revenue for each region in the UK_Regions table from the UK_Sales table and add this as new temporary column. This is super easy and one of the many things you can with the Update Column command.
We will start by opening a table of UK regions table and a point file containin sales figures (UK_Sales). You can also see from the image below, each region contains sales points within them.
With both tables open we will now select the Update Column command. Once again, this is found on the Table tab in the Edit group. When you choose the command, the Update Column dialog box appears.
As mentioned we want to update the UK_Regions table so we select this in the Table to Update drop-down list (see below).
Our goal here is to add a new temporary column to contain the sales information. We do not want to overwrite any of the existing columns in the UK Regions table. It is not possible to add a new temporary column until you have chosen a second table to get data from.
If you try to select 'Add new temporary column' from the 'Column to Update' dropdown menu you will notice it is not an option. You will need to select UK_Sales for Get Value from Table first and then go back and select 'Add new temporary column' in the Column to Update drop-down list.
We want to calculate the value of revenue for each UK regions so we select the appropriate as shown in the image.
Now, we need to ensure that when it calculate the value of revenue for each region that it is looking in the right place. If we do not this MapInfo will just add a new column to the UK_Regions table, however there will be no records. In the Update Column window we need to select 'Join'.
Here we have to ensure we select 'where object from UK_Sales ''is within'' object from table from UK_Regions. Here we are simply telling MapInfo the UK_Sale points lay within the UK_Regions regions and this is how the tables are connected. So MapInfo will calculate the revenue for each point that lays within a specific region and that add to this the UK_Regions table.
Once we have selected OK on both windows you will see a new Browser Window open with the new Revenue column added to the table. You will see, as shown in the image below, MapInfo will have calculated the revenue for each region based on location of the sales points.
Want to learn more?
This article is part of the extensive Query Ninja series. In addition to this update column article, these articles will make you an expert on the querying capabilities of MapInfo Pro. Here is a link to the Topic Catalog where you can explore the Query Ninja series and many other topics!
Article by Mustapha Mudasiru, Associate Software Support Analyst
When not providing impeccable support to our clients, Mustapha enjoys playing sports and hanging around with friends. Mustapha is a also a keen video gamer.