MapInfo Pro

Where expression for Update statements

  • 1.  Where expression for Update statements

    Posted 01-10-2020 09:25
    Edited by Peter Horsbøll Møller 03-18-2020 04:22

    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 start with the improvement to the Update Statement

    Where expression for Update statements

    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 ) ]

    Earlier you would have to query out the records that you want to update and then update the records of this query:
    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

    You can use this when writing MapBasic applications, via the MapBasic Window and via the new SQL Window.

    We haven't yet updated the Update Column dialog to reflect this improvement.

    Note that if you run an Update statement via the Update Column dialog, you can afterwards access this statement via the

    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

    Update SomeTable Set OBJ = CreatePoint(X, Y)
       Where Not OBJ

    Updating X and Y coordinates for records with a spatial object

    Update SomeTable Set X = CentroidX(OBJ), Y = CentroidY(OBJ)
       Where 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!

    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data