MapInfo Pro

I don't know if there is a better place to post (since this post is 4 years old) but I can't find any other forum post that talks about this topic so here it is...How do I get around the inability to directly delete based off a selection made? It keeps giving me a "Cannot Delete from a view" message for the following script:

Select * from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"
Delete * from selection

The select statement works as intended, but it won't allow for the Delete to take place. Replacing "Select *" with "Delete" ends up deleting all the data from my table, not just the data specified from the where clause

e.g. Delete from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"

edit: I should also note that I am currently using MapInfo Pro 2021 Build 172

------------------------------
Derrick Race
Database Coordinator
DIGLINE INCORPORATED
BOISE ID
------------------------------

Hi Derrick,

First, I think you have a typo in your post for the first Delete statement: the correct syntax is "Delete from selection".  However, that won't work in your case because your select statement will produce a view of two joined tables.  Changing your select statement to the following might give you the results you are after:

If wanting to delete from the City_STREETS table:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County")

If wanting to delete from the Cities table:

Select * From Cities Where Cities.City like "City ,County" And obj contains any (Select obj From City_STREETS) 

Then use the Delete statement.

I like to create a named query from which to delete. If taking this approach, the statements become:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County") Into City_STREETS_sel

Delete From City_STREETS_sel

Because you are using a join, the enhanced Delete statement as described in Peter's article above will not work.  You will need to use the two steps: Select the records, then Delete.

Another way of doing this is to:

1. Join the tables together as you originally attempted (e.g. into a named query called "City_STREETS_join")
2. Update a field in the results (e.g. Update City_STREETS_join Set Matched="Y")
3. Select all Matched records from the base table: Select * From City_STREETS Where Matched="Y" Into City_STREETS_sel
4. Delete: Delete From City_STREETS_sel

Another item to address in your query is the following clause:

Cities.City like "City ,County"

Do you mean to select records where Cities.City contains either "City" or "County"?  Then use: Cities.City In ("City", "County")

Do you want to select records where Cities.City contains either "City" or "Count"?  Then use: Cities.City Like "%City%" Or Cities.City Like "%County%"

Or perhaps you want to select records where Cities.City is exactly "City ,County", in which case the "like" keyword will work but is unnecessary; you could use: Cities.City = "City ,County"

Hopefully this helps a little.

I don't know if there is a better place to post (since this post is 4 years old) but I can't find any other forum post that talks about this topic so here it is...How do I get around the inability to directly delete based off a selection made? It keeps giving me a "Cannot Delete from a view" message for the following script:

Select * from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"
Delete * from selection

The select statement works as intended, but it won't allow for the Delete to take place. Replacing "Select *" with "Delete" ends up deleting all the data from my table, not just the data specified from the where clause

e.g. Delete from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"

edit: I should also note that I am currently using MapInfo Pro 2021 Build 172

------------------------------
Derrick Race
Database Coordinator
DIGLINE INCORPORATED
BOISE ID

In addition to the above, I forgot to mention that the sub-select method:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County")

can be a lot slower to execute where you have a lot of data.  The alternative join/update method that I provided as a four-step process in my above reply can help in those situations.

Hi Derrick,

First, I think you have a typo in your post for the first Delete statement: the correct syntax is "Delete from selection".  However, that won't work in your case because your select statement will produce a view of two joined tables.  Changing your select statement to the following might give you the results you are after:

If wanting to delete from the City_STREETS table:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County")

If wanting to delete from the Cities table:

Select * From Cities Where Cities.City like "City ,County" And obj contains any (Select obj From City_STREETS) 

Then use the Delete statement.

I like to create a named query from which to delete. If taking this approach, the statements become:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County") Into City_STREETS_sel

Delete From City_STREETS_sel

Because you are using a join, the enhanced Delete statement as described in Peter's article above will not work.  You will need to use the two steps: Select the records, then Delete.

Another way of doing this is to:

1. Join the tables together as you originally attempted (e.g. into a named query called "City_STREETS_join")
2. Update a field in the results (e.g. Update City_STREETS_join Set Matched="Y")
3. Select all Matched records from the base table: Select * From City_STREETS Where Matched="Y" Into City_STREETS_sel
4. Delete: Delete From City_STREETS_sel

Another item to address in your query is the following clause:

Cities.City like "City ,County"

Do you mean to select records where Cities.City contains either "City" or "County"?  Then use: Cities.City In ("City", "County")

Do you want to select records where Cities.City contains either "City" or "Count"?  Then use: Cities.City Like "%City%" Or Cities.City Like "%County%"

Or perhaps you want to select records where Cities.City is exactly "City ,County", in which case the "like" keyword will work but is unnecessary; you could use: Cities.City = "City ,County"

Hopefully this helps a little.

I don't know if there is a better place to post (since this post is 4 years old) but I can't find any other forum post that talks about this topic so here it is...How do I get around the inability to directly delete based off a selection made? It keeps giving me a "Cannot Delete from a view" message for the following script:

Select * from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"
Delete * from selection

The select statement works as intended, but it won't allow for the Delete to take place. Replacing "Select *" with "Delete" ends up deleting all the data from my table, not just the data specified from the where clause

e.g. Delete from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"

edit: I should also note that I am currently using MapInfo Pro 2021 Build 172

------------------------------
Derrick Race
Database Coordinator
DIGLINE INCORPORATED
BOISE ID

You could speed up the subselect query by using the spatial aggregate AggregateCombine to combine the objects before comparing:

Select * From City_STREETS

   Where obj within (Select AggregateCombine(obj) From Cities Where Cities.City like "City ,County")

This will only return one object to compare against.

In addition to the above, I forgot to mention that the sub-select method:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County")

can be a lot slower to execute where you have a lot of data.  The alternative join/update method that I provided as a four-step process in my above reply can help in those situations.

Hi Derrick,

First, I think you have a typo in your post for the first Delete statement: the correct syntax is "Delete from selection".  However, that won't work in your case because your select statement will produce a view of two joined tables.  Changing your select statement to the following might give you the results you are after:

If wanting to delete from the City_STREETS table:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County")

If wanting to delete from the Cities table:

Select * From Cities Where Cities.City like "City ,County" And obj contains any (Select obj From City_STREETS) 

Then use the Delete statement.

I like to create a named query from which to delete. If taking this approach, the statements become:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County") Into City_STREETS_sel

Delete From City_STREETS_sel

Because you are using a join, the enhanced Delete statement as described in Peter's article above will not work.  You will need to use the two steps: Select the records, then Delete.

Another way of doing this is to:

1. Join the tables together as you originally attempted (e.g. into a named query called "City_STREETS_join")
2. Update a field in the results (e.g. Update City_STREETS_join Set Matched="Y")
3. Select all Matched records from the base table: Select * From City_STREETS Where Matched="Y" Into City_STREETS_sel
4. Delete: Delete From City_STREETS_sel

Another item to address in your query is the following clause:

Cities.City like "City ,County"

Do you mean to select records where Cities.City contains either "City" or "County"?  Then use: Cities.City In ("City", "County")

Do you want to select records where Cities.City contains either "City" or "Count"?  Then use: Cities.City Like "%City%" Or Cities.City Like "%County%"

Or perhaps you want to select records where Cities.City is exactly "City ,County", in which case the "like" keyword will work but is unnecessary; you could use: Cities.City = "City ,County"

Hopefully this helps a little.

I don't know if there is a better place to post (since this post is 4 years old) but I can't find any other forum post that talks about this topic so here it is...How do I get around the inability to directly delete based off a selection made? It keeps giving me a "Cannot Delete from a view" message for the following script:

Select * from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"
Delete * from selection

The select statement works as intended, but it won't allow for the Delete to take place. Replacing "Select *" with "Delete" ends up deleting all the data from my table, not just the data specified from the where clause

e.g. Delete from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"

edit: I should also note that I am currently using MapInfo Pro 2021 Build 172

------------------------------
Derrick Race
Database Coordinator
DIGLINE INCORPORATED
BOISE ID

I also forgot to mention that Peter will have a better solution!

Thanks Peter - I'll make sure I give the AggregateCombine function a go myself.

You could speed up the subselect query by using the spatial aggregate AggregateCombine to combine the objects before comparing:

Select * From City_STREETS

   Where obj within (Select AggregateCombine(obj) From Cities Where Cities.City like "City ,County")

This will only return one object to compare against.

In addition to the above, I forgot to mention that the sub-select method:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County")

can be a lot slower to execute where you have a lot of data.  The alternative join/update method that I provided as a four-step process in my above reply can help in those situations.

Hi Derrick,

First, I think you have a typo in your post for the first Delete statement: the correct syntax is "Delete from selection".  However, that won't work in your case because your select statement will produce a view of two joined tables.  Changing your select statement to the following might give you the results you are after:

If wanting to delete from the City_STREETS table:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County")

If wanting to delete from the Cities table:

Select * From Cities Where Cities.City like "City ,County" And obj contains any (Select obj From City_STREETS) 

Then use the Delete statement.

I like to create a named query from which to delete. If taking this approach, the statements become:

Select * From City_STREETS Where obj within any (Select obj From Cities Where Cities.City like "City ,County") Into City_STREETS_sel

Delete From City_STREETS_sel

Because you are using a join, the enhanced Delete statement as described in Peter's article above will not work.  You will need to use the two steps: Select the records, then Delete.

Another way of doing this is to:

1. Join the tables together as you originally attempted (e.g. into a named query called "City_STREETS_join")
2. Update a field in the results (e.g. Update City_STREETS_join Set Matched="Y")
3. Select all Matched records from the base table: Select * From City_STREETS Where Matched="Y" Into City_STREETS_sel
4. Delete: Delete From City_STREETS_sel

Another item to address in your query is the following clause:

Cities.City like "City ,County"

Do you mean to select records where Cities.City contains either "City" or "County"?  Then use: Cities.City In ("City", "County")

Do you want to select records where Cities.City contains either "City" or "Count"?  Then use: Cities.City Like "%City%" Or Cities.City Like "%County%"

Or perhaps you want to select records where Cities.City is exactly "City ,County", in which case the "like" keyword will work but is unnecessary; you could use: Cities.City = "City ,County"

Hopefully this helps a little.

I don't know if there is a better place to post (since this post is 4 years old) but I can't find any other forum post that talks about this topic so here it is...How do I get around the inability to directly delete based off a selection made? It keeps giving me a "Cannot Delete from a view" message for the following script:

Select * from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"
Delete * from selection

The select statement works as intended, but it won't allow for the Delete to take place. Replacing "Select *" with "Delete" ends up deleting all the data from my table, not just the data specified from the where clause

e.g. Delete from City_STREETS, Cities where Cities.obj contains City_STREETS.obj and Cities.City like "City ,County"

edit: I should also note that I am currently using MapInfo Pro 2021 Build 172

------------------------------
Derrick Race
Database Coordinator
DIGLINE INCORPORATED
BOISE ID