table-cyclist-name

BEGIN BATCH

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    6d5f1663-89c0-45fc-8cfd-60a373b01622,'HOSKINS', 'Melissa'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    38ab64b6-26cc-4de9-ab28-c257cf011659,'FERNANDES', 'Marcia'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    9011d3be-d35c-4a8d-83f7-a3c543789ee7,'NIEWIADOMA', 'Katarzyna'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    95addc4c-459e-4ed7-b4b5-472f19a67995,'ADRIAN', 'Vera'
  );

APPLY BATCH;
SELECT COUNT(lastname) FROM cycling.cyclist_name;
SELECT COUNT(*) FROM cycling.cyclist_name;
DELETE firstname, lastname FROM cycling.cyclist_name
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
DELETE FROM cycling.cyclist_name
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa
  IF firstname = 'Michael' AND lastname = 'Smith';
DELETE FROM cycling.cyclist_name
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0 IF EXISTS;
DELETE FROM cycling.cyclist_name WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 220844bf-4860-49d6-9a4b-6b5d3a79cbfb);
DELETE FROM cycling.cyclist_name WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;
DELETE FROM cycling.cyclist_name
WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498;
DELETE lastname FROM cycling.cyclist_name
  WHERE id = c7fceba0-c141-4207-9494-a29f9809de6f;
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  c4b65263-fe58-4846-83e8-f0e1c13d518f, 'RATTO', 'Rissella'
)
IF NOT EXISTS;
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');
INSERT INTO cycling.cyclist_name
    (id, lastname, firstname)
     VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
//end::data-insert-one[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Inserted one row of data."

tag="data-insert-multiple[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-insert-multiple[]
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');
// end::data-insert-multiple[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Inserted multiple rows of data."

# Query all
tag="select-all[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-all[]
SELECT * FROM cycling.cyclist_name;
// end::select-all[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried all data."

# Query lastname
tag="select-lastname[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-lastname[]
SELECT lastname FROM cycling.cyclist_name;
// end::select-lastname[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried lastname."

# Query lastname, firstname
tag="select-lastname-firstname[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-lastname-firstname[]
SELECT lastname, firstname FROM cycling.cyclist_name;
// end::select-lastname-firstname[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried lastname, firstname."

# LLP: WHAT IS THIS FOR??
# Query initial
tag="select-initial[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-initial[]
SELECT * FROM cycling.cyclist_name;
// end::select-initial[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried all data."

# Query firstname = Marianne
tag="select-marianne[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-marianne[]
SELECT * FROM cycling.cyclist_name WHERE firstname = 'Marianne';
// end::select-marianne[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname equals Marianne."

# Query firstname LIKE 'M%' prefix
tag="select-like-M-prefix[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-like-M-prefix[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE 'M%';
// end::select-like-M-prefix[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname LIKE 'M%' prefix."

# Query will fail: firstname in uppercase
tag="select-uppercase[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-uppercase[]
SELECT * FROM cycling.cyclist_name WHERE firstname = 'MARIANNE';
// end::select-uppercase[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname in uppercase."

# Query will fail: firstname LIKE 'm%' prefix
tag="select-like-m-prefix[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-like-m-prefix[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE m%';
// end::select-like-m-prefix[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname LIKE 'm%' prefix."

# ONLY THE FIRST QUERY WILL RETURN A MATCH
// tag::select-no-match-equality-all[]
SELECT * FROM cycling.cyclist_name WHERE firstname = 'M%';
SELECT * FROM cycling.cyclist_name WHERE firstname = '%M';
SELECT * FROM cycling.cyclist_name WHERE firstname = '%M%';
SELECT * FROM cycling.cyclist_name WHERE firstname = 'm%';
// end::select-no-match-equality-all[]

tag="index-create-firstname-sasi-fn-contains[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-create-firstname-sasi-fn-contains[]
CREATE CUSTOM INDEX IF NOT EXISTS fn_contains ON cycling.cyclist_name (firstname)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = { 'mode': 'CONTAINS' };
// end::index-create-firstname-sasi-fn-contains[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Created index fn_contains on firstname."

# These examples generate an error if they are run before the fn_contains index
# is created
// tag::table-cyclist-name-select-like[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M%';
// end::table-cyclist-name-select-like[]

tag="select_marianne_allow_filtering[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select_marianne_allow_filtering[]
SELECT * FROM cycling.cyclist_name
  WHERE firstname = 'Marianne'
ALLOW FILTERING;
// end::select_marianne_allow_filtering[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname equals Marianne with ALLOW FILTERING."

tag="select-like-M-partial[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-like-M-partial[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M%';
// end::select-like-M-partial[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname LIKE 'M%' partial."

tag="select-arianne[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-arianne[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%arianne';
// end::select-arianne[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname LIKE '%arianne'."

tag="select-arianne-middle[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-arianne-middle[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%arian%';
// end::select-arianne-middle[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname LIKE '%arian%'."

tag="select-greater-Mar[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-greater-Mar[]
SELECT * FROM cycling.cyclist_name WHERE firstname > 'Mar' ALLOW FILTERING;
// end::select-greater-Mar[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname greater than Mar."

# These examples do not return a match when they are run before the
# fn_suffix_allcase index is created
// tag::no_match_partial[]
SELECT * FROM cycling.cyclist_name WHERE firstname = 'MariAnne' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE 'm%';
// end::no_match_partial[]

tag="index-create-fn-suffix-allcase[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-drop-fn-suffix-allcase[]
DROP INDEX IF EXISTS fn_suffix_allcase;
// end::index-drop-fn-suffix-allcase[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Dropped index fn_suffix_allcase."

tag="index-create-firstname-sasi-fn-suffix-allcase[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-create-firstname-sasi-fn-suffix-allcase[]
CREATE CUSTOM INDEX IF NOT EXISTS fn_suffix_allcase
ON cycling.cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
  'mode': 'CONTAINS',
  'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
  'case_sensitive': 'false'
};
// end::index-create-firstname-sasi-fn-suffix-allcase[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Created index fn_suffix_allcase on firstname."

tag="index-describe-firstname-sasi-fn-suffix-allcase[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-describe-firstname-sasi-fn-suffix-allcase[]
DESCRIBE INDEX cycling.fn_suffix_allcase;
// end::index-describe-firstname-sasi-fn-suffix-allcase[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Described index fn_suffix_allcase on firstname."

tag="select-like-m-lower[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-like-m-lower[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
// end::select-like-m-lower[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried firstname LIKE '%m%'."

tag="table-alter-add-age[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::table-alter-add-age[]
ALTER TABLE cycling.cyclist_name
  ADD age int;

UPDATE cycling.cyclist_name SET age = 23
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

INSERT INTO cycling.cyclist_name ( id, age, firstname, lastname )
  VALUES ( 8566eb59-07df-43b1-a21b-666a3c08c08a, 18, 'Marianne', 'DAAE');
// end::table-alter-add-age[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Altered table cyclist_name to add age."

tag="select-name-and-age[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-name-and-age[]
SELECT * FROM cycling.cyclist_name WHERE firstname = 'Marianne' AND age > 20
ALLOW FILTERING;
// end::select-name-and-age[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried name and age."

# Undo the changes in the alter section
DELETE FROM cycling.cyclist_name WHERE id = 8566eb59-07df-43b1-a21b-666a3c08c08a;

ALTER TABLE cycling.cyclist_name
  DROP age;

# Delete data in specified columns from a row,
# changes cyclist Alex Frame to null in name columns
tag="data-delete-column[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-delete-column[]
DELETE firstname, lastname FROM cycling.cyclist_name
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
// end::data-delete-column[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Deleted data in specified columns."

tag="select-after-delete-column-data[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-after-delete-column-data[]
SELECT * FROM cycling.cyclist_name;
// end::select-after-delete-column-data[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried data after deleting columns."

# Delete an entire row, deletes cyclist Alex Frame altogether
tag="data-delete-entire-row[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-delete-entire-row[]
DELETE FROM cycling.cyclist_name
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0 IF EXISTS;
// end::data-delete-entire-row[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Deleted entire row."

tag="select-after-delete-row[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-after-delete-row[]
SELECT * FROM cycling.cyclist_name;
// end::select-after-delete-row[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried data after deleting row."

# Delete a row based on a static column condition,
# attempts to delete cyclist Michael Matthews but lastname does not match
tag="data-delete-condition[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-delete-condition[]
DELETE FROM cycling.cyclist_name
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa
  IF firstname = 'Michael' AND lastname = 'Smith';
// end::data-delete-condition[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Deleted data based on condition."

tag="select-after-delete-condition[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-after-delete-condition[]
SELECT * FROM cycling.cyclist_name;
// end::select-after-delete-condition[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried data after deleting based on condition."

# Delete a row with a WHERE clause, deletes cyclist Steven Kruikswijk
tag="data-delete-row-where[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-delete-row-where[]
DELETE FROM cycling.cyclist_name WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;
// end::data-delete-row-where[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Deleted row based on WHERE clause."

tag="select-after-delete-row-where[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-after-delete-row-where[]
SELECT * FROM cycling.cyclist_name;
// end::select-after-delete-row-where[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried data after deleting row based on WHERE clause."

# Delete rows using an IN clause on a primary key, deletes cyclists Marianne and Paolo
tag="data-delete-in[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-delete-in[]
DELETE FROM cycling.cyclist_name WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 220844bf-4860-49d6-9a4b-6b5d3a79cbfb);
// end::data-delete-in[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Deleted rows based on IN clause."

tag="select-after-delete-in[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-after-delete-in[]
SELECT * FROM cycling.cyclist_name;
// end::select-after-delete-in[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Queried data after deleting rows based on IN clause."

tag="data-delete-lastname[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-delete_lastname[]
DELETE lastname FROM cycling.cyclist_name
  WHERE id = c7fceba0-c141-4207-9494-a29f9809de6f;
// end::data-delete_lastname[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Deleted lastname from row."

# Reinsert data
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');

tag="table-alter-add-comment[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::table-alter-add-comment[]
ALTER TABLE cycling.cyclist_name
  ADD comment text;
// end::table-alter-add-comment[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Altered table cyclist_name to add comment."

tag="update-comment[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::update-comment[]
UPDATE cycling.cyclist_name SET comment = 'Rides hard, gets along with others, a real winner'
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;

UPDATE cycling.cyclist_name SET comment = 'Rides fast, does not get along with others, a real dude'
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
// end::update-comment[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Updated comment."

tag="index-drop-comment-sasi-stdanalyzer-idx[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-drop-comment-sasi-stdanalyzer-idx[]
DROP INDEX IF EXISTS cycling.stdanalyzer_idx;
// end::index-drop-comment-sasi-stdanalyzer-idx[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Dropped index stdanalyzer_idx."

tag="index-create-comment-sasi-stdanalyzer-idx[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-create-comment-sasi-stdanalyzer-idx[]
CREATE CUSTOM INDEX IF NOT EXISTS stdanalyzer_idx
ON cycling.cyclist_name (comment) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
  'mode': 'CONTAINS',
  'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
  'analyzed': 'true',
  'tokenization_skip_stop_words': 'and, the, or',
  'tokenization_enable_stemming': 'true',
  'tokenization_normalize_lowercase': 'true',
  'tokenization_locale': 'en'
};
// end::index-create-comment-sasi-stdanalyzer-idx[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Created index stdanalyzer_idx on comment."

# READ THE DATA
$cqlsh -e "
SOURCE 'cyclist_name-queries.cql';
" >> $results_table
echo "SOURCEd cyclist_name-queries.cql."

# Undo UPDATE statements
UPDATE cycling.cyclist_name SET comment = NULL
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;

UPDATE cycling.cyclist_name SET comment = NULL
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

tag="update-comment-again[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::update-comment-again[]
UPDATE cycling.cyclist_name SET comment = 'Rides hard, gets along with others, a real winner'
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa IF EXISTS;
// end::update-comment-again[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Updated comment again."

tag="update-multi-columns[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::update-multi-columns[]
UPDATE cycling.cyclist_name
SET
  firstname = 'Marianne',
  lastname = 'VOS'
WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;
// end::update-multi-columns[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Updated multiple columns."

tag="update-records[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::update-records[]
UPDATE cycling.cyclist_name SET firstname = NULL
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  fb372533-eb95-4bb4-8685-6ef61e994caa
);
// end::update-records[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Updated records."

tag="upsert[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::upsert[]
UPDATE cycling.cyclist_name
SET
  firstname = 'Anna',
  lastname = 'VAN DER BREGGEN'
WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;
// end::upsert[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Upserted data."

tag="update-if[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::update-if[]
UPDATE cycling.cyclist_name SET comment = 'Rides hard, gets along with others, a real winner'
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa
  IF comment = NULL;
// end::update-if[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Updated data with IF."

# FAILS
tag="update-fail[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::update-fail[]
UPDATE cycling.cyclist_name SET comment = 'Rides hard, gets along with others, a real winner'
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994cac
  IF comment = 'Rides hard, gets along with others, a real winner';
// end::update-fail[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Failed to update data."

tag="count-lastname[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::count-lastname[]
SELECT COUNT(lastname) FROM cycling.cyclist_name;
// end::count-lastname[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Counted lastname."

tag="count-rows[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::count-rows[]
SELECT COUNT(*) FROM cycling.cyclist_name;
// end::count-rows[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Counted rows."

tag="insert-roxxanne[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::insert-roxxane[]
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  4647f6d3-7bd2-4085-8d6c-1229351b5498, 'KNETEMANN', 'Roxxane'
)
IF NOT EXISTS;
// end::insert-roxxane[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Inserted Roxxane."

tag="update-roxxane[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::update-roxxane[]
UPDATE cycling.cyclist_name SET firstname = 'Roxane'
  WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498 IF firstname = 'Roxxane';
// end::update-roxxane[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Updated Roxxane."

tag="data-delete-roxxane[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-delete-roxxane[]
DELETE FROM cycling.cyclist_name
WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498;
// end::data-delete-roxxane[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Deleted Roxxane."
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK', 'Steven'
)
USING TTL 86400
AND TIMESTAMP 123456789;
INSERT INTO cycling.cyclist_name (
  id
) VALUES (
  uuid()
);
CREATE CUSTOM INDEX IF NOT EXISTS stdanalyzer_idx
ON cycling.cyclist_name (comment) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
  'mode': 'CONTAINS',
  'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
  'analyzed': 'true',
  'tokenization_skip_stop_words': 'and, the, or',
  'tokenization_enable_stemming': 'true',
  'tokenization_normalize_lowercase': 'true',
  'tokenization_locale': 'en'
};
CREATE CUSTOM INDEX IF NOT EXISTS fn_contains ON cycling.cyclist_name (firstname)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = { 'mode': 'CONTAINS' };
CREATE CUSTOM INDEX IF NOT EXISTS fn_prefix ON cycling.cyclist_name (firstname)
  USING 'org.apache.cassandra.index.sasi.SASIIndex';
CREATE CUSTOM INDEX IF NOT EXISTS fn_suffix_allcase
ON cycling.cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
  'mode': 'CONTAINS',
  'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
  'case_sensitive': 'false'
};
DESCRIBE INDEX cycling.fn_prefix;
DESCRIBE INDEX cycling.fn_suffix_allcase;
DROP INDEX IF EXISTS cycling.stdanalyzer_idx;
DROP INDEX IF EXISTS fn_contains;
DROP INDEX IF EXISTS fn_notcasesensitive;
DROP INDEX IF EXISTS fn_prefix;
DROP INDEX IF EXISTS fn_sparse;
DROP INDEX IF EXISTS fn_stdanalyzer_idx;
DROP INDEX IF EXISTS fn_suffix_allcase;
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  4647f6d3-7bd2-4085-8d6c-1229351b5498, 'KNETEMANN', 'Roxxane'
)
IF NOT EXISTS;
SELECT * FROM cycling.cyclist_name WHERE firstname = 'MariAnne' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE 'm%';
SELECT * FROM cycling.cyclist_name;
SELECT * FROM cycling.cyclist_name;
SELECT * FROM cycling.cyclist_name;
SELECT * FROM cycling.cyclist_name;
SELECT * FROM cycling.cyclist_name;
SELECT * FROM cycling.cyclist_name;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%arianne';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%arian%';
SELECT * FROM cycling.cyclist_name WHERE firstname > 'Mar' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name;
SELECT lastname FROM cycling.cyclist_name;
SELECT lastname, firstname FROM cycling.cyclist_name;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE 'M%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE m%';
SELECT * FROM cycling.cyclist_name WHERE firstname = 'Marianne';
SELECT * FROM cycling.cyclist_name WHERE firstname = 'Marianne' AND age > 20
ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname = 'M%';
SELECT * FROM cycling.cyclist_name WHERE firstname = '%M';
SELECT * FROM cycling.cyclist_name WHERE firstname = '%M%';
SELECT * FROM cycling.cyclist_name WHERE firstname = 'm%';
SELECT * FROM cycling.cyclist_name WHERE firstname = 'MARIANNE';
SELECT * FROM cycling.cyclist_name
  WHERE firstname = 'Marianne'
ALLOW FILTERING;
ALTER TABLE cycling.cyclist_name
  ADD age int;

UPDATE cycling.cyclist_name SET age = 23
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

INSERT INTO cycling.cyclist_name ( id, age, firstname, lastname )
  VALUES ( 8566eb59-07df-43b1-a21b-666a3c08c08a, 18, 'Marianne', 'DAAE');
ALTER TABLE cycling.cyclist_name
  ADD comment text;
CREATE TABLE IF NOT EXISTS cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);
CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);
CREATE TABLE cycling.cyclist_name (
  id UUID,
  lastname text,
  firstname text,
  PRIMARY KEY (id)
);
CREATE TABLE cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M%';
DESCRIBE TABLE cycling.cyclist_name;
DESCRIBE TABLE cycling.cyclist_name;
DESCRIBE TABLE cycling.cyclist_name;
DESCRIBE TABLE cycling.cyclist_name;
DROP TABLE IF EXISTS cycling.cyclist_name;
TRUNCATE cycling.cyclist_name;
UPDATE cycling.cyclist_name SET comment = 'Rides hard, gets along with others, a real winner'
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;

UPDATE cycling.cyclist_name SET comment = 'Rides fast, does not get along with others, a real dude'
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
UPDATE cycling.cyclist_name SET comment = 'Rides hard, gets along with others, a real winner'
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa IF EXISTS;
UPDATE cycling.cyclist_name SET comment = 'Rides hard, gets along with others, a real winner'
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994cac
  IF comment = 'Rides hard, gets along with others, a real winner';
UPDATE cycling.cyclist_name SET comment = 'Rides hard, gets along with others, a real winner'
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa
  IF comment = NULL;
UPDATE cycling.cyclist_name
SET
  firstname = 'Marianne',
  lastname = 'VOS'
WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;
UPDATE cycling.cyclist_name SET firstname = NULL
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  fb372533-eb95-4bb4-8685-6ef61e994caa
);
UPDATE cycling.cyclist_name SET firstname = 'Roxane'
  WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498 IF firstname = 'Roxxane';
UPDATE cycling.cyclist_name
SET
  firstname = 'Anna',
  lastname = 'VAN DER BREGGEN'
WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2025 DataStax | Privacy policy | Terms of use | Manage Privacy Choices

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com