Reference > AmiScript

AMI SQL: Schema and Data Modification

This page covers AMI SQL commands for modifying data in, and managing the schema of, AMI's in-memory tables.

Data Modification

INSERT...VALUES

Overview

This clause enables the insertion of raw values into an existing table. Many rows may be inserted in a single call. Each values_expr represents a single row of data to be inserted. To insert multiple rows, append additional values_expr.

In order to specify columns in which to insert values, provide an ordered list of column names after the table name. The same number of values must be supplied in the values_expr and in the same order. All other columns will default to null.

If a list of column names is not provided, all columns will be populated. Each values_expr should contain one value for each column in the target table. Values should be in the same natural order as the columns in the target table.

The forloop_expr allows for the insertion of values_expr to be repeated for each cycle of the for loop. The values can reference the for loop's declared variable. Multiple for loops can be nested inside one another.

Syntax

INSERT [INTO] tblname [(col1[, col2 ...])] [forloop_expr ...] VALUES values_expr[, values_expr ...] [LIMIT maxinsert]

tblname: String name of the table to insert into.

colN: String name of the column to contain the Nth value from values_expr.

Examples

INSERT INTO rectangles (area, perimeter) VALUES (20,18);
int length=10,width=5;
INSERT INTO rectangles (area, perimeter) VALUES ((length * width), (2 * length + 2 * width));

INSERT...FROM

Overview

This clause causes the insertion of the result of a projecting-clause (select_clause, analyze_clause, prepare_clause, execute_clause) into an existing table. Each row from the result set of the select_clause will result in the insertion of a new record into the target table. Multiple tables may be inserted in a single call using the UNION clause.

To specify columns in which to insert values, provide an ordered list of column names after the table name. The result set from the preceding select_clause must have the same number of columns. The columns should be in the same order. All other columns will default to null.

If a list of column names is not provided, all columns will be populated. Each values_expr should contain one value for each column in the target table. Values should be in the same natural order as the columns in the target table.

If the BYNAME keyword is included, then source and target columns are aligned based on column name. Column Names in the target table that do not existing in the query clause will be populated with null

Syntax

INSERT [INTO]  tblname [(col1[, col2 ...])] [BYNAME] [FROM]  select_clause
INSERT [INTO]  tblname [(col1[, col2 ...])] [BYNAME] [FROM]  analyze_clause
INSERT [INTO]  tblname [(col1[, col2 ...])] [BYNAME] [FROM]  prepare_clause
INSERT [INTO]  tblname [(col1[, col2 ...])] [BYNAME] [FROM]  execute_clause
INSERT [INTO]  tblname [(col1[, col2 ...])] [BYNAME] [FROM]  describe_clause
INSERT [INTO]  tblname [(col1[, col2 ...])] [BYNAME] [FROM]  show_clause
INSERT [INTO]  tblname [(col1[, col2 ...])] [BYNAME] [FROM]  call_clause

tblname: String name of the table to insert into.

colN: String name of the column to contain the Nth value from results of select clause*.

Example

INSERT INTO employee_summary (age, birthplace) FROM SELECT age, birthplace FROM employee

SYNC

Overview

This clause causes the target table to reflect the result of a projecting-clause (select_clause, analyze_clause, prepare_clause, execute_clause), meaning that rows will be inserted, deleted and updated accordingly on the target table such that the target table will properly mirror the result of the projecting-clause.

In other words, the two examples will result in the same final state for the my_target table, but from a trigger stand point they are significantly different:

//Example 1: using a traditional clear-and-rebuild technique:
 TRUNCATE my_target; INSERT INTO my_target FROM SELECT * from my_source;
//Example 2: using the sync method:
 SYNC my_target FROM SELECT * from my_source;

Note

SYNC is only supported for same-Center tables.

In the first example, the truncate instruction will cause a delete trigger to be fired for every row originally in the my_target table and then the subsequent insert instruction will cause an insert trigger to be fired for every row inserted into the my_target table.

The following rules apply for how the target table is mutated:

• Records in the projecting-clause that do not have a matching record in the target table are inserted into the target table
• Records in the target table that do not have a matching record in the projecting-clause are deleted from the target table
• Records in the target table that have a "perfect" matching record in the projecting-clause remain untouched (no triggers fired)
• Records in the target table that have a "non-perfect" matching record in the projecting-clause are updated

An ON clause can be used to tell the sync tool how to determine if a source and target row are considered equivalent. When an equivalent row between the target table and projecting-clause is identified, this will result in an update. Rows that don't have a match will result in a delete and/or insert.

Matching Rules:

• If all cells between two rows are equivalent, that is considered a "Perfect match"
• If an ON clause is specified: Two row are considered a "non-perfect" match if all of the cells specified in the ON clause are a match, but other cells are not (if an ON clause is not specified then there can not be non-perfect matches)

In order to specify columns in which to sync values, provide an ordered list of column names after the table name (note that only these columns will be considered for the perfect-matching criteria). The result set from the preceding select_clause must have the same number of columns. The columns should be in the same order. All other columns will default to null for inserts and will be left unaltered for updates.

If a list of column names is not provided, all columns will be populated. Each values_expr should contain one value for each column in the target table. Values should be in the same natural order as the columns in the target table.

Syntax

SYNC [INTO]  tblname [(col1[, col2 ...])] [ON(onCol1,[,onCol2])] [FROM]  select_clause
SYNC [INTO]  tblname [(col1[, col2 ...])] [ON(onCol1,[,onCol2])] [FROM]  analyze_clause
SYNC [INTO]  tblname [(col1[, col2 ...])] [ON(onCol1,[,onCol2])] [FROM]  prepare_clause
SYNC [INTO]  tblname [(col1[, col2 ...])] [ON(onCol1,[,onCol2])] [FROM]  execute_clause

tblname: String name of the table to insert into.

colN: String name of the column to contain the Nth value from results of select clause.

onColN: String name of the column to be used in determining if two records are equivalent.

Examples

SYNC accounts USE ds=my_datasource EXECUTE select * from Accounts;
SYNC new_employees SELECT * FROM old_employees;

DELETE FROM

Overview

This clause enables the deletion of specific rows or all rows from a table and also calls the onDelete triggers if applicable.
When it comes to deleting all rows from the table, this is similar to using truncate clause but consumes more memory.
To delete only specific rows, provide a WHERE clause which must evaluate to a Boolean value where true indicates that the row should be deleted. Otherwise, to delete all rows, do not provide a WHERE clause.

Specify the maximum number of rows to delete using the LIMIT clause (maxdelete). Additionally, you may provide a number of rows to skip when deleting (skipdelete). Note the limit is applied after the where clause, meaning that the skipdelete indicates how many records to skip deleting that would otherwise be deleted based on the where clause.

The JOIN .. ON clause allows you to use the values from another table to determine which rows to delete from the target table. The JOIN operates similar to the SELECT's JOIN clause. Note that LEFT JOIN and LEFT ONLY are also supported. Keep in mind that you can only delete rows from the left table.

Syntax

DELETE FROM tblname [WHERE where_expr] [LIMIT [skipdelete,] maxdelete]
DELETE FROM tblname [LEFT [ONLY] JOIN table_expr ON expr ]  [WHERE where_expr] [LIMIT [skipdelete,] maxdelete]
DELETE FROM tblname [LEFT [ONLY] JOIN table_expr ON expr ]  [WHERE where_expr] [LIMIT [skipdelete,] maxdelete]

tblname: String name of the table to delete from.

Examples

//deletes all records from names table
DELETE FROM names;

//deletes 1st & 2nd record
DELETE FROM names LIMIT 2;

//deletes 2nd & 3rd record (offset 1)
DELETE FROM names LIMIT 2,1

//deletes all records where name is Dave
DELETE FROM names WHERE first_name == "Dave";

//delete 1st & 2nd record where name is Dave
DELETE FROM names WHERE first_name == "Dave" LIMIT 2;

//deletes 2nd & 3rd record where name is Dave (offset 1)
DELETE FROM names WHERE first_name == "Dave" LIMIT 2,1;

//deletes all records from names where the first_name has a match with the user's table
DELETE FROM names JOIN users ON first_name == users.name; 

//deletes all records from names where the first_name does NOT have a match with the user's table
DELETE FROM names LEFT ONLY JOIN users ON first_name == users;

TRUNCATE

Overview

This command deletes all rows from a table. You can specify multiple tables, separated by commas.

Syntax

TRUNCATE [TABLE]  tblname [, tblname ...] 

Examples

TRUNCATE TABLE Accounts;
TRUNCATE TABLE Orders, Executions, Alerts;

UPDATE

Overview

This clause can be used to update specific rows or all rows in a table. Following the SET statement, provide an assignment (target=expr) clause for each column to be updated, where target is the name of an existing column.

To update only specific rows, provide a WHERE clause which evaluates to a Boolean value, where true indicates that the row should be updated. Otherwise, to update all rows, do not provide a WHERE clause.

You can specify the maximum number of rows to update using the LIMIT clause (maxupdate). Additionally, you may provide a number of rows to skip when updating (skipupdate). Note the limit is applied after the where clause, meaning that the skipupdate indicates how many records to skip updating that would otherwise be updated based on the where clause.

The JOIN .. ON clause allows you to use the values from another table to update values in the target table. The JOIN operates similar to the SELECT's JOIN clause. Note that LEFT JOIN and LEFT ONLY are also supported. Keep in mind that you can only update values on the left table.

Syntax

UPDATE tblname  SET col1=expr [, col2=expr...] [WHERE where_expr] [LIMIT [skipupdate,] maxupdate]
UPDATE tblname [LEFT [ONLY] JOIN table_expr ON expr ] SET col1=expr [, col2=expr...] [WHERE where_expr] [LIMIT [skipupdate,] maxupdate]
UPDATE tblname [LEFT [ONLY] JOIN table_expr ON expr ] SET col1=expr [, col2=expr...][WHERE where_expr] [LIMIT [skipupdate,] maxupdate]

tblname: String name of the table to update.

colN: String name of the column to assign value from expression.

UPDATE groceries SET price = 10, status=null; //update all records setting price to 10 and status  to null
UPDATE groceries SET price = price + 1 WHERE type == "dairy" //increment price for all records where type is dairy
UPDATE groceries SET price = price + 1 WHERE type == "dairy" LIMIT 10; //increments the price for the first 10 records where type is dairy 
UPDATE groceries SET price = price + 1 WHERE type == "dairy" LIMIT 3,2 ; //increments the price for the 4th and 5th  record where type is dairy 
UPDATE flags set a1=a2,a2=a1 where a1<a2; //Swap the values in the a1 and a2 columns where a1 is less than a2
UPDATE groceries JOIN products ON groceries.id==products.grocery_id SET
price=products.price WHERE type == "dairy"; // Sets the price from the
products table onto the groceries table where the ids match.

Schema Modification

CREATE TABLE

Overview

This command is used to create a new, empty (no rows) table with a specified set of columns (names and types). If another table already exists with the tblname, the existing table will be implicitly dropped and replaced with the newly created table.

At least one column must be specified, and each column must have a unique name.

Syntax

CREATE TABLE [IF NOT EXISTS] tblname (col1 type_expr  [, col2 type_expr ...])

tblname: String name of the table to create.

colN: String name of the Nth column to create.

Example

CREATE TABLE countries (country String, language String, GDP Double)

CREATE TABLE AS

Overview

This command is used to create a new table which has the same schema definition as the result set of a select_clause. If another table already exists with the tblname, the existing table will be implicitly dropped and replaced with the newly created table.

All rows from the select_clause's result set will be inserted into the table.

Syntax

CREATE TABLE [IF NOT EXISTS] tblname[(col1 column_type  [, col2 column_type ...])] AS select_clause
CREATE TABLE [IF NOT EXISTS] tblname[(col1 column_type  [, col2 column_type ...])] AS analyze_clause
CREATE TABLE [IF NOT EXISTS] tblname[(col1 column_type  [, col2 column_type ...])] AS prepare_clause
CREATE TABLE [IF NOT EXISTS] tblname[, tblname ...]  AS execute_clause
CREATE TABLE [IF NOT EXISTS] tblname[(col1 column_type  [, col2 column_type ...])] AS describe_clause
CREATE TABLE [IF NOT EXISTS] tblname[(col1 column_type  [, col2 column_type ...])] AS show_clause
CREATE TABLE [IF NOT EXISTS] tblname[(col1 column_type  [, col2 column_type ...])] AS call_clause

tblname: String name of the table to create.

Example

CREATE TABLE names AS SELECT first_name, last_name FROM employees

ALTER TABLE

Overview

This command alters the schema (column names and types) of an existing table. There are 4 alter commands:

• ADD to add columns

• RENAME ... TO to rename columns

• DROP to delete columns

• MODIFY to change column type

• A best effort cast will be applied. If no appropriate casting exists, the values will become null.

When multiple alterations are supplied, they are evaluated left to right. New columns will be added to the end (right-most) of the table.

Syntax

ALTER TABLE tblname alter_col_expr [, alter_col_expr  ...]

tblname: String name of the table to alter.

Examples

ALTER TABLE AccountHoldings ADD UserId long, RENAME AccountId TO Account_Id, DROP UserInfo
ALTER TABLE AccountHoldings MODIFY UserId AS UserId INT
ALTER TABLE AccountHoldings RENAME UserId TO UserIds, ADD UserId STRING

By default, values in newly added columns will be null. To specify a non-null default value, use the equal operator followed by any additional logic.

Examples

ALTER TABLE AccountHoldings ADD TotalPrice Double = Price*Quantity;
ALTER TABLE AccountHoldings ADD CustomerFullname String = strLower(Forename+Surname);

RENAME TABLE

Overview

This command renames an existing table. If another table already exists with the new name, the existing table will be implicitly dropped and replaced with the renamed table.

Syntax

RENAME TABLE tblname  TO newname

tblname: String name of the table to rename.

newname: String name of the new name for the table.

Example

RENAME TABLE AccountHoldings TO Accounts

DROP TABLE

Overview

This command drops an existing table, along with its schema and all rows. You can specify multiple tables, separated by commas.

Syntax

DROP TABLE [IF EXISTS] tblname [, tblname ...] 

tblname: String name of the table to drop.

Example

DROP TABLE Accounts