Google Git
Sign in
apache / cassandra-website / refs/heads/asf-staging / . / content / doc / 3.11.12 / cassandra / cql / dml.html
blob: 46dcf7efd8157898fa772241224130680918f173 [file] [log] [blame]
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0">
<title>Data Manipulation | Apache Cassandra Documentation</title>
<link rel="stylesheet" href="../../../../assets/css/site.css">
<link rel="schema.dcterms" href="https://purl.org/dc/terms/">
<meta name="dcterms.subject" content="Cassandra">
<meta name="dcterms.identifier" content="3.11">
<meta name="generator" content="Antora 2.3.4">
<link rel="icon" href="../../../../assets/img/favicon.ico" type="image/x-icon">
<script>
const script = document.createElement("script");
const domain = window.location.hostname;
script.type = "text/javascript";
script.src = "https://plausible.cassandra.apache.org/js/plausible.js";
script.setAttribute("data-domain",domain);
script.setAttribute("defer",'true');
script.setAttribute("async",'true');
document.getElementsByTagName("head")[0].appendChild(script);
</script> </head>
<body class="docs-wrapper article">
<div class="container mx-auto relative">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<meta property="og:type" content="website" />
<meta property="og:url" content="/" />
<meta property="og:site_name" content="Apache Cassandra" />
<header id="top-nav">
<div class="inner relative">
<div class="header-social-icons text-right">
<a href="https://twitter.com/cassandra?lang=en" target="_blank" styles="margin-left: 20px;"><img src="../../../../assets/img/twitter-icon-circle-white.svg" alt="twitter icon" width="24"></a>
<a href="https://www.linkedin.com/company/apache-cassandra/" target="_blank" styles="margin-left: 20px;"><img src="../../../../assets/img/LI-In-Bug.png" alt="linked-in icon" width="24"></a>
<a href="https://www.youtube.com/c/PlanetCassandra" target="_blank" styles="margin-left: 20px;"><img src="../../../../assets/img/youtube-icon.png" alt="youtube icon" width="24"></a>
</div>
<div class="cf">
<div class="logo left"><a href="/"><img src="../../../../assets/img/logo-white-r.png" alt="Cassandra Logo"></a></div>
<div class="mobile-nav-icon right">
<img class="toggle-icon" src="../../../../assets/img/hamburger-nav.svg">
</div>
<ul class="main-nav nav-links right flex flex-vert-center flex-space-between">
<li>
<a class="nav-link hide-mobile">Get Started</a>
<ul class="sub-menu bg-white">
<li class="pa-micro">
<a href="/_/cassandra-basics.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-basics.png" alt="cassandra basics icon">
</div>
<div class="sub-nav-text teal py-small">
Cassandra Basics
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/quickstart.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-rocket.png" alt="cassandra basics icon">
</div>
<div class="sub-nav-text teal py-small">
Quickstart
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/ecosystem.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-ecosystem.png" alt="cassandra basics icon">
</div>
<div class="sub-nav-text teal py-small">
Ecosystem
</div>
</a>
</li>
</ul>
</li>
<li><a class="nav-link" href="/doc/latest/">Documentation</a></li>
<li>
<a class="nav-link" href="/_/community.html">Community</a>
<ul class="sub-menu bg-white">
<li class="pa-micro">
<a href="/_/community.html#code-of-conduct">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-welcome.png" alt="welcome icon">
</div>
<div class="sub-nav-text teal py-small">
Welcome
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/community.html#discussions">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-discussions.png" alt="discussions icon">
</div>
<div class="sub-nav-text teal py-small">
Discussions
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/community.html#project-governance">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-governance.png" alt="Governance icon">
</div>
<div class="sub-nav-text teal py-small">
Governance
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/community.html#how-to-contribute">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-contribute.png" alt="Contribute icon">
</div>
<div class="sub-nav-text teal py-small">
Contribute
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/community.html#meet-the-community">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-community.png" alt="Meet the Community icon">
</div>
<div class="sub-nav-text teal py-small">
Meet the Community
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/cassandra-catalyst-program.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-catalyst.png" alt="Catalyst icon">
</div>
<div class="sub-nav-text teal py-small">
Catalyst Program
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/events.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-events.png" alt="Events icon">
</div>
<div class="sub-nav-text teal py-small">
Events
</div>
</a>
</li>
</ul>
</li>
<li>
<a class="nav-link hide-mobile">Learn</a>
<ul class="sub-menu bg-white">
<li class="pa-micro">
<a href="/_/Apache-Cassandra-5.0-Moving-Toward-an-AI-Driven-Future.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-basics.png" alt="Basics icon">
</div>
<div class="sub-nav-text teal py-small">
Cassandra 5.0
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/case-studies.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-case-study.png" alt="Case Studies icon">
</div>
<div class="sub-nav-text teal py-small">
Case Studies
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/resources.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-resources.png" alt="Resources icon">
</div>
<div class="sub-nav-text teal py-small">
Resources
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/blog.html">
<div class="sub-nav-icon">
<img src="../../../../assets/img/sub-menu-blog.png" alt="Blog icon">
</div>
<div class="sub-nav-text teal py-small">
Blog
</div>
</a>
</li>
</ul>
</li>
<li><a class="nav-link btn btn--filled" href="/_/download.html">Download Now</a></li>
</ul>
</div>
</div>
</header>
<div class="hero hero--home grad">
<div class="eye"></div>
<div id="docs-content" class="text-center flex flex-center flex-column relative z2 ma-xlarge">
<h2>Cassandra Documentation</h2>
</div>
</div>
<div class="body px-medium py-medium container">
<div class="docs-nav-bar flex flex-space-between mb-medium">
<div id="mobile-docs-nav-burger" class="hidden">
<svg viewBox="0 0 24 24" width="36" height="36" stroke="#1c81a0" stroke-width="2.5" fill="none" stroke-linecap="round" stroke-linejoin="round" class="css-i6dzq1"><line x1="3" y1="12" x2="21" y2="12"></line><line x1="3" y1="6" x2="21" y2="6"></line><line x1="3" y1="18" x2="21" y2="18"></line></svg>
</div>
<div class="docs-nav-item relative">
<input id="search-input" type="text" placeholder="Search docs">
</div>
<div class="versions-wrapper">
<h4>Version:</h4>
<div class="nav-panel-explore" data-panel="explore">
<div id="version-toggle" class="context">
<span class="version">3.11</span>
</div>
<ul id="versions-list" class="components">
<li class="component">
<ul class="versions">
<li class="version is-latest">
<a href="../../../../_/index.html">master</a>
</li>
</ul>
</li>
<li class="component is-current">
<ul class="versions">
<li class="version">
<a href="../../../trunk/index.html">trunk</a>
</li>
<li class="version">
<a href="../../../5.0/index.html">5.0</a>
</li>
<li class="version is-latest">
<a href="../../../4.1/index.html">4.1</a>
</li>
<li class="version">
<a href="../../../4.0/index.html">4.0</a>
</li>
<li class="version is-current">
<a href="../../index.html">3.11</a>
</li>
</ul>
</li>
</ul>
</div>
</div> </div>
<div class="cf relative">
<nav class="nav docs-nav full-800">
<div class="nav-menu">
<ul class="nav-list">
<li class="nav-item is-active" data-depth="0">
<ul class="nav-list">
<li class="nav-item" data-depth="1">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../index.html">Main</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../../../../_/glossary.html">Glossary</a>
</span>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../../../../_/bugs.html">How to report bugs</a>
</span>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../../../../_/contactus.html">Contact us</a>
</span>
</li>
</ul>
</li>
</ul>
</li>
<li class="nav-item is-active" data-depth="0">
<ul class="nav-list">
<li class="nav-item is-current-path is-active" data-depth="1">
<span class="nav-line">
<button class="nav-toggle"></button>
<span class="nav-text">Cassandra</span>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../getting_started/index.html">Getting Started</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../getting_started/installing.html">Installing Cassandra</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../getting_started/configuring.html">Configuring Cassandra</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../getting_started/querying.html">Inserting and querying</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../getting_started/drivers.html">Client drivers</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../getting_started/production.html">Production recommendations</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../architecture/index.html">Architecture</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../architecture/overview.html">Overview</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../architecture/dynamo.html">Dynamo</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../architecture/storage_engine.html">Storage engine</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../architecture/guarantees.html">Guarantees</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../data_modeling/index.html">Data modeling</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/intro.html">Introduction</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/data_modeling_conceptual.html">Conceptual data modeling</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/data_modeling_rdbms.html">RDBMS design</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/data_modeling_queries.html">Defining application queries</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/data_modeling_logical.html">Logical data modeling</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/data_modeling_physical.html">Physical data modeling</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/data_modeling_refining.html">Evaluating and refining data models</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/data_modeling_schema.html">Defining database schema</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data_modeling/data_modeling_tools.html">Cassandra data modeling tools</a>
</span>
</li>
</ul>
</li>
<li class="nav-item is-current-path is-active" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="index.html">Cassandra Query Language (CQL)</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="definitions.html">Definitions</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="types.html">Data types</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="ddl.html">Data definition (DDL)</a>
</span>
</li>
<li class="nav-item is-current-page is-active" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="dml.html">Data manipulation (DML)</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="operators.html">Operators</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="indexes.html">Secondary indexes</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="mvs.html">Materialized views</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="functions.html">Functions</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="json.html">JSON</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="security.html">Security</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="triggers.html">Triggers</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="appendices.html">Appendices</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="changes.html">Changes</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="SASI.html">SASI</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="cql_singlefile.html">Single file of CQL information</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../configuration/index.html">Configuration</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../configuration/cass_yaml_file.html">cassandra.yaml</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../configuration/cass_rackdc_file.html">cassandra-rackdc.properties</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../configuration/cass_env_sh_file.html">cassandra-env.sh</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../configuration/cass_topo_file.html">cassandra-topologies.properties</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../configuration/cass_cl_archive_file.html">commitlog-archiving.properties</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../configuration/cass_logback_xml_file.html">logback.xml</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../configuration/cass_jvm_options_file.html">jvm-* files</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../operating/index.html">Operating</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="#operating/snitch.adoc">Snitches</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/topo_changes.html">Topology changes</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/repair.html">Repair</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/hints.html">Hints</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/bloom_filters.html">Bloom filters</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/compression.html">Compression</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/cdc.html">Change Data Capture (CDC)</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/backups.html">Backups</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/bulk_loading.html">Bulk loading</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/metrics.html">Metrics</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/security.html">Security</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/hardware.html">Hardware</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/audit_logging.html">Audit logging</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../operating/compaction/index.html">Compaction</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../tools/index.html">Tools</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../tools/cqlsh.html">cqlsh: the CQL shell</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../tools/nodetool/nodetool.html">nodetool</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../tools/sstable/index.html">SSTable tools</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../tools/cassandra_stress.html">cassandra-stress</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../troubleshooting/index.html">Troubleshooting</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../troubleshooting/finding_nodes.html">Finding misbehaving nodes</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../troubleshooting/reading_logs.html">Reading Cassandra logs</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../troubleshooting/use_nodetool.html">Using nodetool</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../troubleshooting/use_tools.html">Using external tools to deep-dive</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../../../_/development/index.html">Development</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/gettingstarted.html">Getting started</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/ide.html">Building and IDE integration</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/testing.html">Testing</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/patches.html">Contributing code changes</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/code_style.html">Code style</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/how_to_review.html">Review checklist</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/how_to_commit.html">How to commit</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/documentation.html">Working on documentation</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/ci.html">Jenkins CI environment</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/dependencies.html">Dependency management</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../_/development/release_process.html">Release process</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../faq/index.html">FAQ</a>
</span>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../plugins/index.html">Plug-ins</a>
</span>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</nav>
<aside class="toc sidebar">
<div class="toc-menu"></div>
</aside>
<main class="article default-main full-800" data-ceiling="topbar">
<div class="article-banner">
<p>A newer version of this documentation is available.</p>
<a class="btn" href="../../../4.1/cassandra/cql/dml.html">View Latest</a>
</div>
<div class="article-header">
<nav class="crumbs" aria-label="breadcrumbs">
<ul>
<li class="crumb">Cassandra</li>
<li class="crumb"><a href="index.html">Cassandra Query Language (CQL)</a></li>
<li class="crumb"><a href="dml.html">Data manipulation (DML)</a></li>
</ul>
</nav>
<div class="tools" role="navigation">
<ul>
<li class="tool edit"><a href="file:///home/jenkins/working/cassandra/doc/modules/cassandra/pages/cql/dml.adoc" title="Edit Page" target="_blank" rel="noopener">Edit</a></li>
</ul>
</div>
</div>
<article class="doc">
<h1 class="page">Data Manipulation</h1>
<div id="preamble">
<div class="sectionbody">
<div class="paragraph">
<p>This section describes the statements supported by CQL to insert,
update, delete and query data.</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="select-statement"><a class="anchor" href="#select-statement"></a><a class="link" href="#select-statement">SELECT</a></h2>
<div class="sectionbody">
<div class="paragraph">
<p>Querying data from data is done using a <code>SELECT</code> statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">select_statement::= SELECT [ JSON | DISTINCT ] ( select_clause | '*' )
FROM `table_name`
[ WHERE `where_clause` ]
[ GROUP BY `group_by_clause` ]
[ ORDER BY `ordering_clause` ]
[ PER PARTITION LIMIT (`integer` | `bind_marker`) ]
[ 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` )_ ] ')'
| COUNT '(' '_' ')'
where_clause::= `relation` ( AND `relation` )*
relation::= column_name operator term
'(' column_name ( ',' column_name )* ')' operator tuple_literal
TOKEN '(' column_name# ( ',' column_name )* ')' operator term
operator::= '=' | '&lt;' | '&gt;' | '&lt;=' | '&gt;=' | '!=' | IN | CONTAINS | CONTAINS KEY
group_by_clause::= column_name ( ',' column_name )*
ordering_clause::= column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*</code></pre>
</div>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT name, occupation FROM users WHERE userid IN (199, 200, 207);
SELECT JSON name, occupation FROM users WHERE userid = 199;
SELECT name AS user_name, occupation AS user_occupation FROM users;
SELECT time, value
FROM events
WHERE event_type = 'myEvent'
AND time &gt; '2011-02-03'
AND time &lt;= '2012-01-01'
SELECT COUNT (*) AS user_count FROM users;</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <code>SELECT</code> statements reads one or more columns for one or more rows
in a table. It returns a result-set of the rows matching the request,
where each row contains the values for the selection corresponding to
the query. Additionally, <a href="functions.html#cql-functions" class="page">functions</a> including
<a href="functions.html#aggregate-functions" class="page">aggregations</a> can be applied to the result.</p>
</div>
<div class="paragraph">
<p>A <code>SELECT</code> statement contains at least a <a href="#selection-clause">selection clause</a> and the name of the table on which
the selection is executed.
CQL does <strong>not</strong> execute joins or sub-queries and a select statement only apply to a single table.
A select statement can also have a <a href="#where-clause">where clause</a> that can further narrow the query results.
Additional clauses can <a href="#ordering-clause">order</a> or <a href="#limit-clause">limit</a> the results.
Lastly, <a href="#allow-filtering">queries that require full cluster filtering</a> can append <code>ALLOW FILTERING</code> to any query.</p>
</div>
<div class="sect2">
<h3 id="selection-clause"><a class="anchor" href="#selection-clause"></a><a class="link" href="#selection-clause">Selection clause</a></h3>
<div class="paragraph">
<p>The <code>select_clause</code> determines which columns will be queried and returned in the result set.
This clause can also apply transformations to apply to the result before returning.
The selection clause consists of a comma-separated list of specific <em>selectors</em> or, alternatively, the wildcard character (<code>*</code>) to select all the columns defined in the table.</p>
</div>
<div class="sect3">
<h4 id="selectors"><a class="anchor" href="#selectors"></a><a class="link" href="#selectors">Selectors</a></h4>
<div class="paragraph">
<p>A <code>selector</code> can be one of:</p>
</div>
<div class="ulist">
<ul>
<li>
<p>A column name of the table selected, to retrieve the values for that
column.</p>
</li>
<li>
<p>A term, which is usually used nested inside other selectors like
functions (if a term is selected directly, then the corresponding column
of the result-set will simply have the value of this term for every row
returned).</p>
</li>
<li>
<p>A casting, which allows to convert a nested selector to a (compatible)
type.</p>
</li>
<li>
<p>A function call, where the arguments are selector themselves. See the
section on <a href="functions.html#cql-functions" class="page">functions</a> for more details.</p>
</li>
<li>
<p>The special call <code>COUNT(*)</code> to the <a href="functions.html#count-function" class="page">COUNT function</a>,
which counts all non-null results.</p>
</li>
</ul>
</div>
</div>
<div class="sect3">
<h4 id="aliases"><a class="anchor" href="#aliases"></a><a class="link" href="#aliases">Aliases</a></h4>
<div class="paragraph">
<p>Every <em>top-level</em> selector can also be aliased (using AS).
If so, the name of the corresponding column in the result set will be
that of the alias. For instance:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">// Without alias
SELECT intAsBlob(4) FROM t;
// intAsBlob(4)
// --------------
// 0x00000004
// With alias
SELECT intAsBlob(4) AS four FROM t;
// four
// ------------
// 0x00000004</code></pre>
</div>
</div>
<div class="admonitionblock note">
<table>
<tr>
<td class="icon">
<i class="fa icon-note" title="Note"></i>
</td>
<td class="content">
<div class="paragraph">
<p>Currently, aliases aren&#8217;t recognized in the <code>WHERE</code> or <code>ORDER BY</code> clauses in the statement.
You must use the orignal column name instead.</p>
</div>
</td>
</tr>
</table>
</div>
</div>
<div class="sect3">
<h4 id="writetime-and-ttl-function"><a class="anchor" href="#writetime-and-ttl-function"></a><a class="link" href="#writetime-and-ttl-function"><code>WRITETIME</code> and <code>TTL</code> function</a></h4>
<div class="paragraph">
<p>Selection supports two special functions that aren&#8217;t allowed anywhere
else: <code>WRITETIME</code> and <code>TTL</code>.
Both functions take only one argument, a column name.
These functions retrieve meta-information that is stored internally for each column:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>WRITETIME</code> stores the timestamp of the value of the column</p>
</li>
<li>
<p><code>TTL</code> stores the remaining time to live (in seconds) for the value of the column if it is set to expire; otherwise the value is <code>null</code>.</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>The <code>WRITETIME</code> and <code>TTL</code> functions can&#8217;t be used on multi-cell columns such as non-frozen
collections or non-frozen user-defined types.</p>
</div>
</div>
</div>
<div class="sect2">
<h3 id="where-clause"><a class="anchor" href="#where-clause"></a><a class="link" href="#where-clause">The <code>WHERE</code> clause</a></h3>
<div class="paragraph">
<p>The <code>WHERE</code> clause specifies which rows are queried. It specifies
a relationship for <code>PRIMARY KEY</code> columns or a column that has
a <a href="indexes.html#create-index-statement" class="page">secondary index</a> defined, along with a set value.</p>
</div>
<div class="paragraph">
<p>Not all relationships are allowed in a query. For instance, only an equality
is allowed on a partition key. The <code>IN</code> clause is considered an equality for one or more values.
The <code>TOKEN</code> clause can be used to query for partition key non-equalities.
A partition key must be specified before clustering columns in the <code>WHERE</code> clause. The relationship
for clustering columns must specify a <strong>contiguous</strong> set of rows to order.</p>
</div>
<div class="paragraph">
<p>For instance, given:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">CREATE TABLE posts (
userid text,
blog_title text,
posted_at timestamp,
entry_title text,
content text,
category int,
PRIMARY KEY (userid, blog_title, posted_at)
);</code></pre>
</div>
</div>
<div class="paragraph">
<p>The following query is allowed:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT entry_title, content FROM posts
WHERE userid = 'john doe'
AND blog_title='John''s Blog'
AND posted_at &gt;= '2012-01-01' AND posted_at &lt; '2012-01-31';</code></pre>
</div>
</div>
<div class="paragraph">
<p>But the following one is not, as it does not select a contiguous set of
rows (and we suppose no secondary indexes are set):</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">// Needs a blog_title to be set to select ranges of posted_at
SELECT entry_title, content FROM posts
WHERE userid = 'john doe'
AND posted_at &gt;= '2012-01-01' AND posted_at &lt; '2012-01-31';</code></pre>
</div>
</div>
<div class="paragraph">
<p>When specifying relationships, the <code>TOKEN</code> function can be applied to the <code>PARTITION KEY</code> column to query.
Rows will be selected based on the token of the <code>PARTITION_KEY</code> rather than on the value.</p>
</div>
<div class="admonitionblock important">
<table>
<tr>
<td class="icon">
<i class="fa icon-important" title="Important"></i>
</td>
<td class="content">
<div class="paragraph">
<p>The token of a key depends on the partitioner in use, and that
in particular the <code>RandomPartitioner</code> won&#8217;t yield a meaningful order.
Also note that ordering partitioners always order token values by bytes (so
even if the partition key is of type int, <code>token(-1) &gt; token(0)</code> in
particular).</p>
</div>
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM posts
WHERE token(userid) &gt; token('tom') AND token(userid) &lt; token('bob');</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <code>IN</code> relationship is only allowed on the last column of the
partition key or on the last column of the full primary key.</p>
</div>
<div class="paragraph">
<p>It is also possible to “group” <code>CLUSTERING COLUMNS</code> together in a
relation using the tuple notation.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM posts
WHERE userid = 'john doe'
AND (blog_title, posted_at) &gt; ('John''s Blog', '2012-01-01');</code></pre>
</div>
</div>
<div class="paragraph">
<p>This query will return all rows that sort after the one having “John&#8217;s Blog” as
<code>blog_tile</code> and '2012-01-01' for <code>posted_at</code> in the clustering order. In
particular, rows having a <code>post_at &#8656; '2012-01-01'</code> will be returned, as
long as their <code>blog_title &gt; 'John''s Blog'</code>.</p>
</div>
<div class="paragraph">
<p>That would not be the case for this example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM posts
WHERE userid = 'john doe'
AND blog_title &gt; 'John''s Blog'
AND posted_at &gt; '2012-01-01';</code></pre>
</div>
</div>
<div class="paragraph">
<p>The tuple notation may also be used for <code>IN</code> clauses on clustering columns:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM posts
WHERE userid = 'john doe'
AND (blog_title, posted_at) IN (('John''s Blog', '2012-01-01'), ('Extreme Chess', '2014-06-01'));</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <code>CONTAINS</code> operator may only be used for collection columns (lists,
sets, and maps). In the case of maps, <code>CONTAINS</code> applies to the map
values. The <code>CONTAINS KEY</code> operator may only be used on map columns and
applies to the map keys.</p>
</div>
</div>
<div class="sect2">
<h3 id="group-by-clause"><a class="anchor" href="#group-by-clause"></a><a class="link" href="#group-by-clause">Grouping results</a></h3>
<div class="paragraph">
<p>The <code>GROUP BY</code> option can condense all selected
rows that share the same values for a set of columns into a single row.</p>
</div>
<div class="paragraph">
<p>Using the <code>GROUP BY</code> option, rows can be grouped at the partition key or clustering column level.
Consequently, the <code>GROUP BY</code> option only accepts primary key columns in defined order as arguments.
If a primary key column is restricted by an equality restriction, it is not included in the <code>GROUP BY</code> clause.</p>
</div>
<div class="paragraph">
<p>Aggregate functions will produce a separate value for each group.
If no <code>GROUP BY</code> clause is specified, aggregates functions will produce a single value for all the rows.</p>
</div>
<div class="paragraph">
<p>If a column is selected without an aggregate function, in a statement
with a <code>GROUP BY</code>, the first value encounter in each group will be
returned.</p>
</div>
</div>
<div class="sect2">
<h3 id="ordering-clause"><a class="anchor" href="#ordering-clause"></a><a class="link" href="#ordering-clause">Ordering results</a></h3>
<div class="paragraph">
<p>The <code>ORDER BY</code> clause selects the order of the returned results.
The argument is a list of column names and each column&#8217;s order
(<code>ASC</code> for ascendant and <code>DESC</code> for descendant,
The possible orderings are limited by the <a href="ddl.html#clustering-order" class="page">clustering order</a> defined on the table:</p>
</div>
<div class="ulist">
<ul>
<li>
<p>if the table has been defined without any specific <code>CLUSTERING ORDER</code>, then the order is as defined by the clustering columns
or the reverse</p>
</li>
<li>
<p>otherwise, the order is defined by the <code>CLUSTERING ORDER</code> option and the reversed one.</p>
</li>
</ul>
</div>
</div>
<div class="sect2">
<h3 id="limit-clause"><a class="anchor" href="#limit-clause"></a><a class="link" href="#limit-clause">Limiting results</a></h3>
<div class="paragraph">
<p>The <code>LIMIT</code> option to a <code>SELECT</code> statement limits the number of rows
returned by a query. The <code>PER PARTITION LIMIT</code> option limits the
number of rows returned for a given partition by the query. Both types of limits can used in the same statement.</p>
</div>
</div>
<div class="sect2">
<h3 id="allow-filtering"><a class="anchor" href="#allow-filtering"></a><a class="link" href="#allow-filtering">Allowing filtering</a></h3>
<div class="paragraph">
<p>By default, CQL only allows select queries that don&#8217;t involve a full scan of all partitions.
If all partitions are scanned, then returning the results may experience a significant latency proportional to the
amount of data in the table. The <code>ALLOW FILTERING</code> option explicitly executes a full scan. Thus, the performance of
the query can be unpredictable.</p>
</div>
<div class="paragraph">
<p>For example, consider the following table of user profiles with birth year and country of residence.
The birth year has a secondary index defined.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">CREATE TABLE users (
username text PRIMARY KEY,
firstname text,
lastname text,
birth_year int,
country text
);
CREATE INDEX ON users(birth_year);</code></pre>
</div>
</div>
<div class="paragraph">
<p>The following queries are valid:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">// All users are returned
SELECT * FROM users;
// All users with a particular birth year are returned
SELECT * FROM users WHERE birth_year = 1981;</code></pre>
</div>
</div>
<div class="paragraph">
<p>In both cases, the query performance is proportional to the amount of data returned.
The first query returns all rows, because all users are selected.
The second query returns only the rows defined by the secondary index, a per-node implementation; the results will
depend on the number of nodes in the cluster, and is indirectly proportional to the amount of data stored.
The number of nodes will always be multiple number of magnitude lower than the number of user profiles stored.
Both queries may return very large result sets, but the addition of a <code>LIMIT</code> clause can reduced the latency.</p>
</div>
<div class="paragraph">
<p>The following query will be rejected:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR';</code></pre>
</div>
</div>
<div class="paragraph">
<p>Cassandra cannot guarantee that large amounts of data won&#8217;t have to scanned amount of data, even if the result is small.
If you know that the dataset is small, and the performance will be reasonable, add <code>ALLOW FILTERING</code> to allow the query to
execute:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING;</code></pre>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="insert-statement"><a class="anchor" href="#insert-statement"></a><a class="link" href="#insert-statement">INSERT</a></h2>
<div class="sectionbody">
<div class="paragraph">
<p>Inserting data for a row is done using an <code>INSERT</code> statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">insert_statement::= INSERT INTO table_name ( names_values | json_clause )
[ IF NOT EXISTS ]
[ USING update_parameter ( AND update_parameter )* ]
names_values::= names VALUES tuple_literal
json_clause::= JSON string [ DEFAULT ( NULL | UNSET ) ]
names::= '(' column_name ( ',' column_name )* ')'</code></pre>
</div>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">INSERT INTO NerdMovies (movie, director, main_actor, year)
VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion', 2005)
USING TTL 86400;
INSERT INTO NerdMovies JSON '{"movie": "Serenity", "director": "Joss Whedon", "year": 2005}';</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <code>INSERT</code> statement writes one or more columns for a given row in a
table.
Since a row is identified by its <code>PRIMARY KEY</code>, at least one columns must be specified.
The list of columns to insert must be supplied with the <code>VALUES</code> syntax.
When using the <code>JSON</code> syntax, <code>VALUES</code> are optional.
See the section on <a href="#cql-json">JSON support</a> for more detail.
All updates for an <code>INSERT</code> are applied atomically and in isolation.</p>
</div>
<div class="paragraph">
<p>Unlike in SQL, <code>INSERT</code> does not check the prior existence of the row by default.
The row is created if none existed before, and updated otherwise.
Furthermore, there is no means of knowing which action occurred.</p>
</div>
<div class="paragraph">
<p>The <code>IF NOT EXISTS</code> condition can restrict the insertion if the row does not exist.
However, note that using <code>IF NOT EXISTS</code> will incur a non-negligible performance cost, because Paxos is used,
so this should be used sparingly.</p>
</div>
<div class="paragraph">
<p>Please refer to the <a href="#update-parameters">UPDATE</a> section for informations on the <code>update_parameter</code>.
Also note that <code>INSERT</code> does not support counters, while <code>UPDATE</code> does.</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="update-statement"><a class="anchor" href="#update-statement"></a><a class="link" href="#update-statement">UPDATE</a></h2>
<div class="sectionbody">
<div class="paragraph">
<p>Updating a row is done using an <code>UPDATE</code> statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">update_statement ::= UPDATE table_name
[ USING update_parameter ( AND update_parameter )* ]
SET assignment( ',' assignment )*
WHERE where_clause
[ IF ( EXISTS | condition ( AND condition)*) ]
update_parameter ::= ( TIMESTAMP | TTL ) ( 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</code></pre>
</div>
</div>
<div class="paragraph">
<p>For instance:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">UPDATE NerdMovies USING TTL 400
SET director = 'Joss Whedon',
main_actor = 'Nathan Fillion',
year = 2005
WHERE movie = 'Serenity';
UPDATE UserActions
SET total = total + 2
WHERE user = B70DE1D0-9908-4AE3-BE34-5573E5B09F14
AND action = 'click';</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <code>UPDATE</code> statement writes one or more columns for a given row in a
table.
The <code>WHERE`clause is used to select the row to update and must include all columns of the `PRIMARY KEY</code>.
Non-primary key columns are set using the <code>SET</code> keyword.
In an <code>UPDATE</code> statement, all updates within the same partition key are applied atomically and in isolation.</p>
</div>
<div class="paragraph">
<p>Unlike in SQL, <code>UPDATE</code> does not check the prior existence of the row by default.
The row is created if none existed before, and updated otherwise.
Furthermore, there is no means of knowing which action occurred.</p>
</div>
<div class="paragraph">
<p>The <code>IF</code> condition can be used to choose whether the row is updated or not if a particular condition is met.
However, like the <code>IF NOT EXISTS</code> condition, a non-negligible performance cost can be incurred.</p>
</div>
<div class="paragraph">
<p>Regarding the <code>SET</code> assignment:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>c = c + 3</code> will increment/decrement counters, the only operation allowed.
The column name after the '=' sign <strong>must</strong> be the same than the one before the '=' sign.
Increment/decrement is only allowed on counters.
See the section on <a href="#counters">counters</a> for details.</p>
</li>
<li>
<p><code>id = id + &lt;some-collection&gt;</code> and <code>id[value1] = value2</code> are for collections.
See the <a href="types.html#collections" class="page">collections</a> for details.</p>
</li>
<li>
<p><code>id.field = 3</code> is for setting the value of a field on a non-frozen user-defined types.
See the <a href="types.html#udts" class="page">UDTs</a> for details.</p>
</li>
</ul>
</div>
<div class="sect2">
<h3 id="update-parameters"><a class="anchor" href="#update-parameters"></a><a class="link" href="#update-parameters">Update parameters</a></h3>
<div class="paragraph">
<p><code>UPDATE</code> and <code>INSERT</code> statements support the following parameters:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>TTL</code>: specifies an optional Time To Live (in seconds) for the
inserted values. If set, the inserted values are automatically removed
from the database after the specified time. Note that the TTL concerns
the inserted values, not the columns themselves. This means that any
subsequent update of the column will also reset the TTL (to whatever TTL
is specified in that update). By default, values never expire. A TTL of
0 is equivalent to no TTL. If the table has a default_time_to_live, a
TTL of 0 will remove the TTL for the inserted or updated values. A TTL
of <code>null</code> is equivalent to inserting with a TTL of 0.</p>
</li>
</ul>
</div>
<div class="paragraph">
<p><code>UPDATE</code>, <code>INSERT</code>, <code>DELETE</code> and <code>BATCH</code> statements support the following parameters:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>TIMESTAMP</code>: sets the timestamp for the operation. If not specified,
the coordinator will use the current time (in microseconds) at the start
of statement execution as the timestamp. This is usually a suitable
default.</p>
</li>
</ul>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="delete_statement"><a class="anchor" href="#delete_statement"></a><a class="link" href="#delete_statement">DELETE</a></h2>
<div class="sectionbody">
<div class="paragraph">
<p>Deleting rows or parts of rows uses the <code>DELETE</code> statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">delete_statement::= DELETE [ simple_selection ( ',' simple_selection ) ]
FROM table_name
[ USING update_parameter ( AND update_parameter# )* ]
WHERE where_clause
[ IF ( EXISTS | condition ( AND condition)*) ]</code></pre>
</div>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">DELETE FROM NerdMovies USING TIMESTAMP 1240003134
WHERE movie = 'Serenity';
DELETE phone FROM Users
WHERE userid IN (C73DE1D3-AF08-40F3-B124-3FF3E5109F22, B70DE1D0-9908-4AE3-BE34-5573E5B09F14);</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <code>DELETE</code> statement deletes columns and rows. If column names are
provided directly after the <code>DELETE</code> keyword, only those columns are
deleted from the row indicated by the <code>WHERE</code> clause. Otherwise, whole
rows are removed.</p>
</div>
<div class="paragraph">
<p>The <code>WHERE</code> clause specifies which rows are to be deleted. Multiple rows
may be deleted with one statement by using an <code>IN</code> operator. A range of
rows may be deleted using an inequality operator (such as <code>&gt;=</code>).</p>
</div>
<div class="paragraph">
<p><code>DELETE</code> supports the <code>TIMESTAMP</code> option with the same semantics as in
<a href="#update-parameters">updates</a>.</p>
</div>
<div class="paragraph">
<p>In a <code>DELETE</code> statement, all deletions within the same partition key are
applied atomically and in isolation.</p>
</div>
<div class="paragraph">
<p>A <code>DELETE</code> operation can be conditional through the use of an <code>IF</code>
clause, similar to <code>UPDATE</code> and <code>INSERT</code> statements. However, as with
<code>INSERT</code> and <code>UPDATE</code> statements, this will incur a non-negligible
performance cost because Paxos is used, and should be used sparingly.</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="batch_statement"><a class="anchor" href="#batch_statement"></a><a class="link" href="#batch_statement">BATCH</a></h2>
<div class="sectionbody">
<div class="paragraph">
<p>Multiple <code>INSERT</code>, <code>UPDATE</code> and <code>DELETE</code> can be executed in a single
statement by grouping them through a <code>BATCH</code> statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">batch_statement ::= BEGIN [ UNLOGGED | COUNTER ] BATCH
[ USING update_parameter( AND update_parameter)* ]
modification_statement ( ';' modification_statement )*
APPLY BATCH
modification_statement ::= insert_statement | update_statement | delete_statement</code></pre>
</div>
</div>
<div class="paragraph">
<p>For instance:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">BEGIN BATCH
INSERT INTO users (userid, password, name) VALUES ('user2', 'ch@ngem3b', 'second user');
UPDATE users SET password = 'ps22dhds' WHERE userid = 'user3';
INSERT INTO users (userid, password) VALUES ('user4', 'ch@ngem3c');
DELETE name FROM users WHERE userid = 'user1';
APPLY BATCH;</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <code>BATCH</code> statement group multiple modification statements
(insertions/updates and deletions) into a single statement. It serves
several purposes:</p>
</div>
<div class="ulist">
<ul>
<li>
<p>It saves network round-trips between the client and the server (and
sometimes between the server coordinator and the replicas) when batching
multiple updates.</p>
</li>
<li>
<p>All updates in a <code>BATCH</code> belonging to a given partition key are
performed in isolation.</p>
</li>
<li>
<p>By default, all operations in the batch are performed as <em>logged</em>, to
ensure all mutations eventually complete (or none will). See the notes
on <a href="#unlogged-batches">UNLOGGED batches</a> for more details.</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>Note that:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>BATCH</code> statements may only contain <code>UPDATE</code>, <code>INSERT</code> and <code>DELETE</code>
statements (not other batches for instance).</p>
</li>
<li>
<p>Batches are <em>not</em> a full analogue for SQL transactions.</p>
</li>
<li>
<p>If a timestamp is not specified for each operation, then all
operations will be applied with the same timestamp (either one generated
automatically, or the timestamp provided at the batch level). Due to
Cassandra&#8217;s conflict resolution procedure in the case of
<a href="http://wiki.apache.org/cassandra/FAQ#clocktie">timestamp ties</a>,
operations may be applied in an order that is different from the order
they are listed in the <code>BATCH</code> statement. To force a particular
operation ordering, you must specify per-operation timestamps.</p>
</li>
<li>
<p>A LOGGED batch to a single partition will be converted to an UNLOGGED
batch as an optimization.</p>
</li>
</ul>
</div>
<div class="sect2">
<h3 id="unlogged-batches"><a class="anchor" href="#unlogged-batches"></a><a class="link" href="#unlogged-batches"><code>UNLOGGED</code> batches</a></h3>
<div class="paragraph">
<p>By default, Cassandra uses a batch log to ensure all operations in a
batch eventually complete or none will (note however that operations are
only isolated within a single partition).</p>
</div>
<div class="paragraph">
<p>There is a performance penalty for batch atomicity when a batch spans
multiple partitions. If you do not want to incur this penalty, you can
tell Cassandra to skip the batchlog with the <code>UNLOGGED</code> option. If the
<code>UNLOGGED</code> option is used, a failed batch might leave the patch only
partly applied.</p>
</div>
</div>
<div class="sect2">
<h3 id="counter-batches"><a class="anchor" href="#counter-batches"></a><a class="link" href="#counter-batches"><code>COUNTER</code> batches</a></h3>
<div class="paragraph">
<p>Use the <code>COUNTER</code> option for batched counter updates. Unlike other
updates in Cassandra, counter updates are not idempotent.</p>
</div>
</div>
</div>
</div>
</article>
</main>
</div>
</div>
<footer class="grad grad--two flex-center pb-xlarge">
<div class="inner text-center z2 relative">
<h2 class="white py-small">Get started with Cassandra, fast.</h2>
<a id="footer-cta" href="/_/quickstart.html" class="btn btn--filled ma-medium">Quickstart Guide</a>
</div>
<div class="inner flex flex-distribute-items mt-xlarge z2 relative">
<div class="col-2">
<div id="footer-logo" class="logo logo--footer mb-medium"><img src="../../../../assets/img/logo-white-r.png" alt="Cassandra Logo"></div>
<p>Apache Cassandra<img src="../../../../assets/img/registered.svg" alt="®" style="width:18px;"> powers mission-critical deployments with improved performance and unparalleled levels of scale in the cloud.</p>
<div class="footer-social-icons">
<a href="https://twitter.com/cassandra?lang=en" target="_blank"><img src="../../../../assets/img/twitter-icon-circle-white.svg" alt="twitter icon" width="24"></a>
<a href="https://www.linkedin.com/company/apache-cassandra/" target="_blank"><img src="../../../../assets/img/LI-In-Bug.png" alt="linked-in icon" width="24"></a>
<a href="https://www.youtube.com/c/PlanetCassandra" target="_blank"><img src="../../../../assets/img/youtube-icon.png" alt="youtube icon" width="24"></a>
</div>
</div>
<div class="col-2 flex flex-center">
<ul class="columns-2">
<li class="mb-small"><a href="/">Home</a></li>
<li class="mb-small"><a href="/_/cassandra-basics.html">Cassandra Basics</a></li>
<li class="mb-small"><a href="/_/quickstart.html">Quickstart</a></li>
<li class="mb-small"><a href="/_/ecosystem.html">Ecosystem</a></li>
<li class="mb-small"><a href="/doc/latest/">Documentation</a></li>
<li class="mb-small"><a href="/_/community.html">Community</a></li>
<li class="mb-small"><a href="/_/case-studies.html">Case Studies</a></li>
<li class="mb-small"><a href="/_/resources.html">Resources</a></li>
<li class="mb-small"><a href="/_/blog.html">Blog</a></li>
</ul>
</div>
</div>
</footer>
<div class="lower-footer bg-white pa-medium">
<div class="flex flex-row flex-vert-center">
<div class="pr-medium"><img src="../../../../assets/img//feather-small.png" alt="ASF" width="20"></div>
<div class="pr-medium"><a href="http://www.apache.org/" target="_blank">Foundation</a></div>
<div class="pr-medium"><a href="https://www.apache.org/events/current-event.html" target="_blank">Events</a></div>
<div class="pr-medium"><a href="https://www.apache.org/licenses/" target="_blank">License</a></div>
<div class="pr-medium"><a href="https://www.apache.org/foundation/thanks" target="_blank">Thanks</a></div>
<div class="pr-medium"><a href="https://www.apache.org/security" target="_blank">Security</a></div>
<div class="pr-medium"><a href="https://privacy.apache.org/policies/privacy-policy-public.html" target="_blank">Privacy</a></div>
<div class="pr-medium"><a href="https://www.apache.org/foundation/sponsorship" target="_blank">Sponsorship</a></div>
</div>
<p class="my-medium">© 2009-<script>document.write(new Date().getFullYear())</script> <a href="https://apache.org" target="_blank">The Apache Software Foundation</a> under the terms of the Apache License 2.0. Apache, the Apache feather logo, Apache Cassandra, Cassandra, and the Cassandra logo, are either registered trademarks or trademarks of The Apache Software Foundation.</p>
</div>
<div id="fade" class="hidden"></div>
<div id="modal" class="hidden">
<div id="close-modal" class="cursor-pointer"><svg viewBox="0 0 24 24" width="24" height="24" stroke="currentColor" stroke-width="2" fill="none" stroke-linecap="round" stroke-linejoin="round" class="css-i6dzq1"><line x1="18" y1="6" x2="6" y2="18"></line><line x1="6" y1="6" x2="18" y2="18"></line></svg></div>
<div id="mod-content" class="vid-mod-content resp-container"></div>
</div>
<script src="../../../../assets/js/site.js"></script>
<script async src="../../../../assets/js/vendor/highlight.js"></script>
<script src="../../../../assets/js/vendor/lunr.js"></script>
<script src="../../../../assets/js/vendor/search.js" id="search-script" data-base-path="../../../.." data-page-path="/Cassandra/3.11/cassandra/cql/dml.html"></script>
<script async src="../../../../assets/../search-index.js"></script>
<script>
jQuery(function(){
var windowW = $(window).width();
$(document)
.on('click','.mobile-nav-icon',function(){
$('.main-nav').fadeIn();
})
.on('click','.main-nav',function(){
if(windowW <= 1000){
$(this).fadeOut();
}
})
.on('click','#version-toggle',function(){
$(this).toggleClass('active');
$(this).next().fadeToggle();
})
.on('click','#mobile-docs-nav-burger', function(){
$(this).toggleClass('active');
$('.docs-nav').toggleClass('active');
});
var url = window.location.pathname;
var isQuickstart = url.includes('quickstart.html');
if(isQuickstart){
var footerCTA = document.getElementById('footer-cta');
footerCTA.innerHTML = 'Get latest updates';
footerCTA.setAttribute('href', '/_/blog.html');
}
});
</script>
</div>
</body>
</html>