SQL - Assign To

GSAK (Geocaching Swiss Army Knife)

SQL - Assign To

Warning: The SQL command was the first introduction of SQL to the macro language. It has many limitations and quirks and is now deprecated. It is provided for here for backwards compatibility only. It is very much recommended that you use the more complete and robust SQLite database engine. See the SQLite() function.

Allows you to assign the result of a SELECT statement to a named temporary memory-resident table that can be referred to in subsequent statements. (This is a non-standard SQL statement). ASSIGN TO is like a variable assignment but can assign an entire SQL table. You can create very complex compound queries with ASSIGN TO.

This table is created in memory only. If you subsequently want to make this a disk based table then you must use SAVE TABLE

Syntax:

ASSIGN TO tablename selectstatement

Example:

$status = sql("ASSIGN TO temp SELECT code,name FROM caches","")

If tablename already exists as a disk based table in the database then an error occurs.

When tablename does not exist in the database but was already assigned to then the existing table is overwritten.

Notes
Make sure that you use output field alias names when you ASSIGN TO using a SELECT with joined tables.

When you execute the ASSIGN TO the given name will be assigned to the new table and the table itself will not be released until you use RELEASE TABLE.

COMMIT has no effect on "memory only" tables created with ASSIGN TO. To convert these "memory only" tables to "disk based" tables, use SAVE TABLE