MapInfo SQL Commands

The MapInfo SQL Language is based on the SQL-3 and SQL/MM standards. String literals must be enclosed in single quotation marks ('example') while identifiers (column names, table names, aliases, etc.) should be enclosed in double quotation marks ("example identifier") if necessary. Identifiers only need to be quoted if the parsing logic is unable to correctly parse the identifier. This would include identifiers that have spaces in their names or other special characters. See Quote Rules for more information.

In Spectrum Spatial, the MapInfo SQL Language consists of the following commands:

Select

SELECT < select_list >
	FROM { < table_source > } [ ,...n ]
	[ WHERE < search_condition > ]
	[ GROUP BY expression [ ,...n ] ]
	[ ORDER BY < column_position | column_name > [ ASC | DESC ]  [ ,...n ]]
	[ LIMIT positive_integer ]
	[ OFFSET positive_integer ]


	< select_list > ::=
		{
			*
			| { table_name | table_alias }.*
			| { expression } [ [ AS ] column_alias ]
		} [ ,...n ]

	< table_source > ::=
		table_name [ [ AS ] table_alias ]

Note: You cannot perform an ORDER BY or GROUP BY on a raster, geometry, style, or binary column.

Note: NULL values for ORDER BY ASC will be at the bottom of the order. ORDER BY DESC values will be at the top..

Update

UPDATE <table_name>
   SET {column_name = expression} [ ,…n]
   [ WHERE < search_condition > ]
   [ COMMIT_INTERVAL = integer ]

For example, to update a column called DENSITY in all states use the following string:

UPDATE STATES set DENSITY = POP_1990 / MI_AREA(obj, 'sq mi', 'Spherical')

If you want to update the DENSITY column for certain states, add a WHERE clause:

UPDATE STATES set DENSITY = POP_1990 / MI_AREA(obj, 'sq mi', 'Spherical') WHERE STATE_NAME in ('New York', 'New Jersey')

You can also specify a commit interval (the number of changes committed as a batch in one transaction):

UPDATE STATES set DENSITY = POP_1990 / MI_AREA(obj, 'sq mi', 'Spherical') COMMIT_INTERVAL 30

Insert

INSERT [INTO] { table_name } [ ( column_list ) ]
{ VALUES ({expression | NULL}[, ...n])

Delete

DELETE [FROM] { table_name } [ WHERE < search_condition > ]

	< search_condition > ::=
		{ [ NOT ] < predicate > | ( < search_condition > ) }
			[ { AND | OR } [ NOT ] { < predicate > | ( < search_condition > ) }  [ ,...n ] ]

	< predicate > ::=
		{
			expression [ { = | < > | != | > | >= | < | <= } expression ]
			| string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ]
			| expression [ NOT ] BETWEEN expression AND expression
			| expression IS [ NOT ] NULL
		}

expression is a column name, pseudo column, column alias, constant, function, or any combination of column names, column aliases, constants, and functions connected by an operator(s). Column names and pseudo columns may be prefixed with a table name or a table alias followed by the dot (".") character.

search_condition is a reference to an expression that results in either true or false. Most of the functions in this document can be used in search conditions, but aggregation functions are not supported for this use.

group_by_expression is a reference to a column in the select list - either an exact copy of the select list expression, the alias, a 1-based number indicating the position of the column, or coln where n is a number representing a column.

order_by_expression is a reference to a column in the select list - either an exact copy of the select list expression, the alias, a 1-based number indicating the position of the column, or coln where n is a number representing a column.