MapInfo Pro

 View Only

MapInfo Monday: Using SQL Window to update the Selection

  • 1.  MapInfo Monday: Using SQL Window to update the Selection

    Posted 02-06-2023 04:13
    Happy #MapInfoMonday!

    In today's article, I wanted to show how you can use the SQL Window to create a small script to update a few columns in the current selection.​

    Often you may have to work your way through your dataset, check some values, and then update some columns so you can see how far you have come. You can change the value in the status column via the Info window, or you can run an Update statement. Often users are using the Update Column dialog to execute the Update statement for a column.

    The Update Column dialog works fine if you only need to update one column with the same value each time. Imagine you work your way through many records and need to assign the value GOOD, AVERAGE, or POOR to the Status column for each. In this case, there is a big chance that you will have to change the value in the Update Column dialog every time.

    And then add that you also need to update the CheckedDate column with the date when you changed the value. That doesn't make things easier, eh?

    But there is another way. You can update your table via the SQL Window. Let me show you some of the benefits.

    As an example, I have a table with Cell Towers that I need to inspect and update. I have selected a single cell tower from the map below. You can also have a browser window of the cell tower table and select from this and so easily run through the list.

    To use the SQL Window, I need to open it from the SQL dropdown on the Table, Map, or Spatial tab.

    Typically, I have the SQL Window open on the right side of the MapInfo Pro window. You can also have it floating if you prefer. The window stays on the screen when you execute the statement. This makes it easy to use the statement over and over again as you don't have to open a dialog all the time.

    From the Create New Script list at the upper right side of the SQL Window, I can create a new script from a template. I pick Update Statement. Notice how the text in the Edit field changes.

    I'm looking to update the current Selection. So make sure that you select a record from the table you want to update. This makes it possible for MapInfo Pro to show you the column names.

    Here's the first basic statement to update the column STATUS with the value GOOD.
    Update Selection
       Set STATUS = "GOOD"​

    I can extend this to update multiple columns by separating the individual column assignments by a comma. I also want to update the column CheckedDate with the current date. I get the current date from the MapBasic function called CurDate().
    Update Selection
       Set STATUS = "GOOD"​, CheckedDate = CurDate()​

    Now my small script will update two columns in the current selection. It looks like this in the SQL Window. Notice that I also unchecked the Browse Result at the bottom of the SQL Window as I don't want to see the result in a browser.

    I can click the Rename Script button in the Scripts List to rename the current script. This makes it easier for me to later find it again. Let me rename it to Update Status GOOD. I hit the Enter key when I'm done typing the new name.

    I now hit the Save Script button and the Mark as Favorite button in the Scripts List to save the script to a file and to make it a favorite script that I often use. Notice how the Favorite star gets yellow when you click it.

    I can now use the script from the SQL Window simply by clicking on the Run button. This will update the selected record(s).

    As I marked my script as a favorite script, I can also find and run it from the SQL dropdown. When I click on the script in the dropdown, it will be executed. This means that I can run the Update statement without seeing any dialog.

    Using Variables

    As I mentioned at the beginning, I have to update the records with three different values for the STATUS. The solution above updates all with the value GOOD. So I could now create additional two: one for AVERAGE and one for POOR. This could work as long as I only have three options but what if I had 5, 10, or 50?

    Let's look at another approach where the value we use, can be selected by the user.

    From the Variables list, select Edit Variables. This is often the only option you see in the list. When you click this option, the dialog Edit Variables will appear.

    Variables allow you to add a level of flexibility to your scripts. They allow you to prompt the user for input. From the Edit Variables dialog, you can control the questions you ask the user.

    The dialog starts by presenting you with a variable named v1 of type String. Let's change this to match our needs.

    I start by changing the name of the variable to match what it does. In my example, it holds the Status value to use. I name it sStatus. The s prefix tells me that it is of type String.

    For the Variable Type, I select List from the Type List in the Edit Variables dialog. I could also have stuck to the String type. But by using a List, I can control the values that the user can choose from. As you can see below, there is a long list of possible variable types to choose from.

    For Prompt I enter Status and as the Default Value I enter selection.STATUS. This will read the value from the selection and use this as the default value selected.

    And finally, in the field Values I enter the values that I want the user to select from. Separate the values with semicolons (;) and make sure the entire string is quoted. I wrote "GOOD;AVERAGE;POOR".

    Now hit the OK button to save this variable with your script.

    Back in the SQL Window, I can now use my variable. I can either select it from the Variables dropdown.

    Or if I remember the name, I can just type it in. Notice that I have replaced the hardcoded status with my variable name sStatus. Make sure to save the changes to your script. The small Asterix (*) next to the script name tells you that you have unsaved changes.

    When I run the script now, it will prompt me to select the status for the selected records.

    I have only used one variable in my example. If I had used more, the dialog shown would show more controls allowing you to specify the value for the other variables, too.

    If you have selected more than one record or if the current value for the selected record isn't found in the list, the dialog will warn you to select a valid value from the list. You will not be able to continue until you meet the requirements for all the controls.

    I hope this has given you some ideas on how to improve the way you update columns, especially when you are updating multiple columns with variable values many times.

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