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
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
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
, or Spatial
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
I'm looking to update the current
. 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
with the value
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
with the current date. I get the current date from the MapBasic function called
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
statement without seeing any dialog.
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
. So I could now create additional two: one for
and one for
. 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
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
. The s prefix tells me that it is of type
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.
and as the Default Value
. 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
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
Or if I remember the name, I can just type it in. Notice that I have replaced the hardcoded status with my variable name
. 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