DML Statements (Data Manipulation Language) in Amazon Keyspaces

Data manipulation language (DML) is the set of CQL statements that you use to manage data in Amazon Keyspaces (for Apache Cassandra) tables. You use DML statements to add, modify, or delete data in a table.

You also use DML statements to query data in a table. (Note that CQL doesn't support joins or subqueries.)

SELECT

Use a SELECT statement to query data.

Syntax

select_statement ::=  SELECT ( select_clause | '*' )
                      FROM table_name
                      [ WHERE where_clause ]
                      [ LIMIT (integer | bind_marker) ]
                      [ ALLOW FILTERING ]
select_clause    ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector         ::=  column_name
                      | term
                      | CAST '(' selector AS cql_type ')'
                      | function_name '(' [ selector ( ',' selector )* ] ')'
where_clause     ::=  relation ( AND relation )*
relation         ::=  column_name operator term
                      '(' column_name ( ',' column_name )* ')' operator tuple_literal
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | CONTAINS | CONTAINS KEY
ordering_clause  ::=  column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*

Example

SELECT name, id, manager_id FROM "myGSGKeyspace".employees_tbl ;

Ordering results

The ORDER BY clause specifies the sort order of the returned results. It takes as arguments a list of column names along with the sort order for each column. You only can specify clustering columns in ordering clauses. Non-clustering columns are not allowed.

The sort order options are ASC for ascending and DESC for descending sort order. If the sort order is omitted, the default ordering of the clustering column is used. The possible sort orders you can use in an ordering clause depend on the sort order assigned to each clustering column at table creation. Query results can only be sorted in the order defined for all clustering columns at table creation or the inverse of the defined sort order. For example, if the tables CLUSTERING ORDER is (col1 ASC, col2 DESC, col3 ASC), then the valid parameters for ORDER BY are either (col1 ASC, col2 DESC, col3 ASC) or (col1 DESC, col2 ASC, col3 DESC). For more information on CLUSTERING ORDER, see table_options under CREATE TABLE.

Example

SELECT name, id, division, manager_id FROM "myGSGKeyspace".employees_tbl ORDER BY division;

INSERT

Use the INSERT statement to add a row to a table.

Syntax

insert_statement ::=  INSERT INTO table_name ( names_values )
                      [ IF NOT EXISTS ]
names_values     ::=  names VALUES tuple_literal
names            ::=  '(' column_name ( ',' column_name )* ')'

Example

INSERT INTO "myGSGKeyspace".employees_tbl (id, name, project, region, division, role, pay_scale, vacation_hrs, manager_id)
VALUES ('012-34-5678','Russ','NightFlight','US','Engineering','IC',3,12.5, '234-56-7890') ;

UPDATE

Use the UPDATE statement to modify a row in a table.

Syntax

update_statement ::=  UPDATE table_name
                      [ USING update_parameter ( AND update_parameter )* ]
                      SET assignment ( ',' assignment )*
                      WHERE where_clause
                      [ IF ( EXISTS | condition ( AND condition )*) ]
update_parameter ::=  ( integer | bind_marker )
assignment       ::=  simple_selection '=' term
                     | column_name '=' column_name ( '+' | '-' ) term
                     | column_name '=' list_literal '+' column_name
simple_selection ::=  column_name
                     | column_name '[' term ']'
                     | column_name '.' `field_name
condition        ::=  simple_selection operator term

Example

UPDATE "myGSGKeyspace".employees_tbl SET pay_scale = 5 WHERE id = '567-89-0123' AND division = 'Marketing' ;

To increment a counter, use the following syntax. For more information, see Counters.

UPDATE ActiveUsers SET counter = counter + 1  WHERE user = A70FE1C0-5408-4AE3-BE34-8733E5K09F14 AND action = 'click';

DELETE

Use the DELETE statement to remove a row from a table.

Syntax

delete_statement ::=  DELETE [ simple_selection ( ',' simple_selection ) ]
                      FROM table_name

                      WHERE where_clause
                      [ IF ( EXISTS | condition ( AND condition )*) ]

simple_selection ::=  column_name
                     | column_name '[' term ']'
                     | column_name '.' `field_name

condition        ::=  simple_selection operator term

Where:

• table_name is the table that contains the row you want to delete.

Example

DELETE manager_id FROM "myGSGKeyspace".employees_tbl WHERE id='789-01-2345' AND division='Executive' ;