MapInfo Pro

Where expression for Delete statements

  • 1.  Where expression for Delete statements

    Posted 01-15-2020 21:03
    Edited by Peter Horsbøll Møller 01-20-2020 03:08

    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.

    This post will look at the improvement to the Delete Statement

    Where expression for Delete statements

    Just like the Update statement, the Delete statement has only supported a very limited Where condition; You could only specify a ROWID of the record that should be deleted

    Delete From SomeTable Where ROWID = 1

    This has, however, been very useful when writing MapBasic applications where you loop through a table and need to delete individual records.

    With MapInfo Pro v2019, we have finally added support for a more complex Where condition for the Delete statement. The new syntax looks like this:

    Delete [ Object ] From table
      [ Where [RowId id_number] | where_expr]
      [ DropIndex ( Auto | On | Off ) ]

    Earlier you would have to query out the records that you wanted to delete and then delete the records of this query:

    Select * From SomeTable
       Where Not OBJ 
       Into __TO__DELETE NoSelect
    Delete From __TO__DELETE
    Close Table __TO__DELETE

    With MapInfo Pro v2019, you can do this in a single statement:

    Delete From SomeTable
       Where Not OBJ

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

    We don't have a dedicated dialog that helps you query out and delete specific records. Users will most either manually select the records and then hit the Delete key to delete the records or they would run a query to select the records they want to delete and they hit the Delete key.

    This also means that this change will mean more to the MapBasic developers. However, as you saw above, the new SQL Window now lets users run their delete statements too.

    A few more examples of possible Delete statements:

    Deleting records with X and Y values of 0

    Delete From SomeTable
       Where X <> 0 And Y <> 0

    Deleting records that don't have a spatial object

    Delete From SomeTable
       Where Not OBJ

    Deleting records from a table of polygons where the polygon has less than 3 nodes

    Delete From SomeTable
       Where Val(Str$(ObjectInfo(OBJ, OBJ_INFO_NPNTS))) < 3

    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 v2019!

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