There's more to experience when you log in!
Over the coming weeks, I'll write a #series of articles about the #SQL improvements we have made in MapInfo Pro v2019. This will, of course, look at the Select statement but I'll also dive into the other improvements.
We will state with the improvement to the Update Statement
For many years, MapInfo Pro has supported a Where condition when running SQL Update statements. This has mostly been used by MapBasic developers because the Update Column dialog doesn't give you access to this capability.
And when you look closer at it, it's also quite limited; you could specify what specific record you wanted to update by specifying a ROWID:
Update SomeTable Set NAME = "Peter" Where ROWID = 1
This has been very useful when writing MapBasic applications where you loop through a table and need to update the individual records.
With MapInfo Pro v2019, we have finally added support for a more complex Where condition for the Update statement. The new syntax looks like this:
Update table Set column = update_expr [ , column = update_expr, ...]
[ Where [RowId = id_number] | where_expr]
[ DropIndex ( Auto | On | Off ) ]
Select * From SomeTable
Where AREA = 0
Into __To__Update NoSelect
Update __TO__UPDATE Set AREA = Area(obj, "sq km")Close Table __To__Update
With MapInfo Pro v2019, you can do this in a single statement:
Update SomeTable Set AREA = Area(obj, "sq km")
Where AREA = 0
We haven't yet updated the Update Column dialog to reflect this improvement.
Scripts dropdown in the new SQL Window.
A few more examples of possible Update statements:
Creating points for records with coordinates, ignoring X and Y values of 0
Update SomeTable Set OBJ = CreatePoint(X, Y)
Where X <> 0 And Y <> 0
Creating points for records that do not already have a spatial object
Where Not OBJ
Updating X and Y coordinates for records with a spatial object
Update SomeTable Set X = CentroidX(OBJ), Y = CentroidY(OBJ)
Do you find this improvement useful? How will you take advantage of it?
Stay tuned for more details on the improvements to the SQL language of MapInfo Pro!