MapInfo Pro

MapInfo Monday: Update Existing Query through the SQL Select dialog in MapInfo Pro v2021.1

  • 1.  MapInfo Monday: Update Existing Query through the SQL Select dialog in MapInfo Pro v2021.1

    Posted 05-09-2022 10:05
    Edited by Peter Møller 05-09-2022 10:07
    Happy #MaqpInfoMonday​

    Today, we will look at another small improvement that made it into MapInfo pro v2021.1: Updating an Existing Query.

    The Refresh Query itself isn't new in MapInfo Pro v2021.1. We introduced this in v2021. Here you could refresh an existing query through the context menu in the Table List. In MapInfo Pro v2021.1, we added this capability to a number of the existing dialogs. We added this to the SQL Select dialog, the Select by Attribute dialog, and the Select by Location dialog.

    In the example below, I want to create a map for three different areas. You can see my initial map and the browser for my data. The values in the column Areaname controls when to highlight specific points. I'm looking to create a map for the three small villages in my data: Mølby, Ørsted and Oksenvad. And yes, these are names of real villages in Denmark.

    I'll start by selecting the records for the village Ørsted using the SQL Select dialog. Notice how the condition looks for a string inside a potential longer string using the Like operator and the % as wildcards:
    Areaname Like "%Ørsted%"

    I also check the option Add Results To Current Map Window to get the result added as a new layer that I can style differently. And I entered a name for the query result so that it will be given a name that I can easily recognize: _qAreaOfInterest.

    Once the query has been added to my map, I right-click on the layer and use View Entire Layer to zoom to the extent of the selected records.

    After I style the layer and configure the labels, I end up with a map like the one below.

    Now that was one map. I have two more to do. In order to keep the work I already have done with the layer style and labels, I decide to just update the existing query with a new condition. Let me show you how that works.

    In the SQL Select dialog, I first make sure that the field into Table Named holds the name of the query that I want to update. In this case, it's already setup with that name as I just used the dialog to create the initial query.

    Then I check the option Update Existing to tell MapInfo Pro update the existing query. Note that this option is only enabled if the into Table Named contains the name of an open query. Also note that when you check this option, the Select Columns and from Tables fields get disabled. The same goes for the Group by Columns and Order by Columns fields. That's because it is limited what you can change when it comes to updating an existing query. It basically just updating the query using the original Select statement or you can modify the Where part of query.

    In our example, we will do exactly that: We change the value in the where Condition field so that it will select records for another village:
    Areaname Like "%Mølby%"

    When I now click OK; MapInfo Pro will update the existing query to contain the records that meet the new condition.

    In older versions of MapInfo Pro, you can overwrite an existing query with a new. But this has the side effect that the query will be closed and recreated which means it will be gone from all window. By updating the query instead, the query stays in all windows and keeps the style and label settings you may have applied to it.

    When the query has been updated, I can right-click the layer again and use View Entire Layer to zoom to the new extent

    You can also update an existing query through MapBasic if you prefer. The Select statement from above looks like this:
    Select Update For _qAreaOfInterest
      where Areaname Like "%Mølby%"

    I hope that you find this improvement helpful.

    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data