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.
This post will look at the improvement to the Delete Statement
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__DELETEClose 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
Where X <> 0 And Y <> 0
Deleting records that don't have a spatial object
Deleting records from a table of polygons where the polygon has less than 3 nodes
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!