blob: 670802ea10836c3c106b8034cd749b90b6950532 [file] [log] [blame]
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="The Apache Cassandra database is the right choice when you need scalability and high availability without compromising performance. Linear scalability and proven fault-tolerance on commodity hardware or cloud infrastructure make it the perfect platform for mission-critical data. Cassandra's support for replicating across multiple datacenters is best-in-class, providing lower latency for your users and the peace of mind of knowing that you can survive regional outages.
">
<meta name="keywords" content="cassandra, apache, apache cassandra, distributed storage, key value store, scalability, bigtable, dynamo" />
<meta name="robots" content="index,follow" />
<meta name="language" content="en" />
<title>Documentation</title>
<link rel="canonical" href="http://cassandra.apache.org/doc/4.0-alpha4/cql/dml.html">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">
<link rel="stylesheet" href="./../../../css/style.css">
<link rel="stylesheet" href="./../../../css/sphinx.css">
<link rel="top" title="Apache Cassandra Documentation v4.0-alpha4" href="../index.html"/> <link rel="up" title="The Cassandra Query Language (CQL)" href="index.html"/> <link rel="next" title="Secondary Indexes" href="indexes.html"/> <link rel="prev" title="Data Definition" href="ddl.html"/>
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.2.0/css/all.css" integrity="sha384-hWVjflwFxL6sNzntih27bfxkr27PmbbK/iSvJ+a4+0owXq79v+lsFkW54bOGbiDQ" crossorigin="anonymous">
<link type="application/atom+xml" rel="alternate" href="http://cassandra.apache.org/feed.xml" title="Apache Cassandra Website" />
</head>
<body>
<!-- breadcrumbs -->
<div class="topnav">
<div class="container breadcrumb-container">
<ul class="breadcrumb">
<li>
<div class="dropdown">
<img class="asf-logo" src="./../../../img/asf_feather.png" />
<a data-toggle="dropdown" href="#">Apache Software Foundation <span class="caret"></span></a>
<ul class="dropdown-menu" role="menu" aria-labelledby="dLabel">
<li><a href="http://www.apache.org">Apache Homepage</a></li>
<li><a href="http://www.apache.org/licenses/">License</a></li>
<li><a href="http://www.apache.org/foundation/sponsorship.html">Sponsorship</a></li>
<li><a href="http://www.apache.org/foundation/thanks.html">Thanks</a></li>
<li><a href="http://www.apache.org/security/">Security</a></li>
</ul>
</div>
</li>
<li><a href="./../../../">Apache Cassandra</a></li>
<li><a href="./../../../doc/latest/">Documentation</a></li>
<li><a href="./">The Cassandra Query Language (CQL)</a></li>
<li>Data Manipulation</li>
</ul>
</div>
<!-- navbar -->
<nav class="navbar navbar-default navbar-static-top" role="navigation">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#cassandra-menu" aria-expanded="false">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="./../../../"><img src="./../../../img/cassandra_logo.png" alt="Apache Cassandra logo" /></a>
</div><!-- /.navbar-header -->
<div id="cassandra-menu" class="collapse navbar-collapse">
<ul class="nav navbar-nav navbar-right">
<li><a href="./../../../">Home</a></li>
<li><a href="./../../../download/">Download</a></li>
<li><a href="./../../../doc/latest/">Documentation</a></li>
<li><a href="./../../../community/">Community</a></li>
<li>
<a href="./../../../blog/">Blog</a>
</li>
</ul>
</div><!-- /#cassandra-menu -->
</div>
</nav><!-- /.navbar -->
</div><!-- /.topnav -->
<div class="container-fluid">
<div class="row">
<div class="col-md-3">
<div class="doc-navigation">
<div class="doc-menu" role="navigation">
<div class="navbar-header">
<button type="button" class="pull-left navbar-toggle" data-toggle="collapse" data-target=".sidebar-navbar-collapse">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
</div>
<div class="navbar-collapse collapse sidebar-navbar-collapse">
<form id="doc-search-form" class="navbar-form" action="../search.html" method="get" role="search">
<div class="form-group">
<input type="text" size="30" class="form-control input-sm" name="q" placeholder="Search docs">
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</div>
</form>
<ul class="current">
<li class="toctree-l1"><a class="reference internal" href="../getting_started/index.html">Getting Started</a></li>
<li class="toctree-l1"><a class="reference internal" href="../new/index.html">New Features in Apache Cassandra 4.0</a></li>
<li class="toctree-l1"><a class="reference internal" href="../architecture/index.html">Architecture</a></li>
<li class="toctree-l1 current"><a class="reference internal" href="index.html">The Cassandra Query Language (CQL)</a><ul class="current">
<li class="toctree-l2"><a class="reference internal" href="definitions.html">Definitions</a></li>
<li class="toctree-l2"><a class="reference internal" href="types.html">Data Types</a></li>
<li class="toctree-l2"><a class="reference internal" href="ddl.html">Data Definition</a></li>
<li class="toctree-l2 current"><a class="current reference internal" href="#">Data Manipulation</a><ul>
<li class="toctree-l3"><a class="reference internal" href="#select">SELECT</a></li>
<li class="toctree-l3"><a class="reference internal" href="#insert">INSERT</a></li>
<li class="toctree-l3"><a class="reference internal" href="#update">UPDATE</a></li>
<li class="toctree-l3"><a class="reference internal" href="#delete">DELETE</a></li>
<li class="toctree-l3"><a class="reference internal" href="#batch">BATCH</a></li>
</ul>
</li>
<li class="toctree-l2"><a class="reference internal" href="indexes.html">Secondary Indexes</a></li>
<li class="toctree-l2"><a class="reference internal" href="mvs.html">Materialized Views</a></li>
<li class="toctree-l2"><a class="reference internal" href="security.html">Security</a></li>
<li class="toctree-l2"><a class="reference internal" href="functions.html">Functions</a></li>
<li class="toctree-l2"><a class="reference internal" href="operators.html">Arithmetic Operators</a></li>
<li class="toctree-l2"><a class="reference internal" href="json.html">JSON Support</a></li>
<li class="toctree-l2"><a class="reference internal" href="triggers.html">Triggers</a></li>
<li class="toctree-l2"><a class="reference internal" href="appendices.html">Appendices</a></li>
<li class="toctree-l2"><a class="reference internal" href="changes.html">Changes</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../data_modeling/index.html">Data Modeling</a></li>
<li class="toctree-l1"><a class="reference internal" href="../configuration/index.html">Configuring Cassandra</a></li>
<li class="toctree-l1"><a class="reference internal" href="../operating/index.html">Operating Cassandra</a></li>
<li class="toctree-l1"><a class="reference internal" href="../tools/index.html">Cassandra Tools</a></li>
<li class="toctree-l1"><a class="reference internal" href="../troubleshooting/index.html">Troubleshooting</a></li>
<li class="toctree-l1"><a class="reference internal" href="../development/index.html">Contributing to Cassandra</a></li>
<li class="toctree-l1"><a class="reference internal" href="../faq/index.html">Frequently Asked Questions</a></li>
<li class="toctree-l1"><a class="reference internal" href="../plugins/index.html">Third-Party Plugins</a></li>
<li class="toctree-l1"><a class="reference internal" href="../bugs.html">Reporting Bugs</a></li>
<li class="toctree-l1"><a class="reference internal" href="../contactus.html">Contact us</a></li>
</ul>
</div><!--/.nav-collapse -->
</div>
</div>
</div>
<div class="col-md-8">
<div class="content doc-content">
<div class="content-container">
<div class="section" id="data-manipulation">
<span id="id1"></span><h1>Data Manipulation<a class="headerlink" href="#data-manipulation" title="Permalink to this headline"></a></h1>
<p>This section describes the statements supported by CQL to insert, update, delete and query data.</p>
<div class="section" id="select">
<span id="select-statement"></span><h2>SELECT<a class="headerlink" href="#select" title="Permalink to this headline"></a></h2>
<p>Querying data from data is done using a <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> statement:</p>
<pre>
<strong id="grammar-token-select-statement">select_statement</strong> ::= SELECT [ JSON | DISTINCT ] ( <a class="reference internal" href="#grammar-token-select-clause"><code class="xref docutils literal notranslate"><span class="pre">select_clause</span></code></a> | '*' )
FROM <a class="reference internal" href="ddl.html#grammar-token-table-name"><code class="xref docutils literal notranslate"><span class="pre">table_name</span></code></a>
[ WHERE <a class="reference internal" href="#grammar-token-where-clause"><code class="xref docutils literal notranslate"><span class="pre">where_clause</span></code></a> ]
[ GROUP BY <a class="reference internal" href="#grammar-token-group-by-clause"><code class="xref docutils literal notranslate"><span class="pre">group_by_clause</span></code></a> ]
[ ORDER BY <a class="reference internal" href="#grammar-token-ordering-clause"><code class="xref docutils literal notranslate"><span class="pre">ordering_clause</span></code></a> ]
[ PER PARTITION LIMIT (<a class="reference internal" href="definitions.html#grammar-token-integer"><code class="xref docutils literal notranslate"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind-marker"><code class="xref docutils literal notranslate"><span class="pre">bind_marker</span></code></a>) ]
[ LIMIT (<a class="reference internal" href="definitions.html#grammar-token-integer"><code class="xref docutils literal notranslate"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind-marker"><code class="xref docutils literal notranslate"><span class="pre">bind_marker</span></code></a>) ]
[ ALLOW FILTERING ]
<strong id="grammar-token-select-clause">select_clause </strong> ::= <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><span class="pre">selector</span></code></a> [ AS <a class="reference internal" href="definitions.html#grammar-token-identifier"><code class="xref docutils literal notranslate"><span class="pre">identifier</span></code></a> ] ( ',' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><span class="pre">selector</span></code></a> [ AS <a class="reference internal" href="definitions.html#grammar-token-identifier"><code class="xref docutils literal notranslate"><span class="pre">identifier</span></code></a> ] )
<strong id="grammar-token-selector">selector </strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a>
| <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a>
| CAST '(' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><span class="pre">selector</span></code></a> AS <a class="reference internal" href="types.html#grammar-token-cql-type"><code class="xref docutils literal notranslate"><span class="pre">cql_type</span></code></a> ')'
| <a class="reference internal" href="functions.html#grammar-token-function-name"><code class="xref docutils literal notranslate"><span class="pre">function_name</span></code></a> '(' [ <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><span class="pre">selector</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><span class="pre">selector</span></code></a> )* ] ')'
| COUNT '(' '*' ')'
<strong id="grammar-token-where-clause">where_clause </strong> ::= <a class="reference internal" href="#grammar-token-relation"><code class="xref docutils literal notranslate"><span class="pre">relation</span></code></a> ( AND <a class="reference internal" href="#grammar-token-relation"><code class="xref docutils literal notranslate"><span class="pre">relation</span></code></a> )*
<strong id="grammar-token-relation">relation </strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal notranslate"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a>
'(' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* ')' <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal notranslate"><span class="pre">operator</span></code></a> <a class="reference internal" href="types.html#grammar-token-tuple-literal"><code class="xref docutils literal notranslate"><span class="pre">tuple_literal</span></code></a>
TOKEN '(' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* ')' <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal notranslate"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a>
<strong id="grammar-token-operator">operator </strong> ::= '=' | '&lt;' | '&gt;' | '&lt;=' | '&gt;=' | '!=' | IN | CONTAINS | CONTAINS KEY
<strong id="grammar-token-group-by-clause">group_by_clause </strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )*
<strong id="grammar-token-ordering-clause">ordering_clause </strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> [ ASC | DESC ] ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> [ ASC | DESC ] )*
</pre>
<p>For instance:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">occupation</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">userid</span> <span class="k">IN</span> <span class="p">(</span><span class="mf">199</span><span class="p">,</span> <span class="mf">200</span><span class="p">,</span> <span class="mf">207</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="k">JSON</span> <span class="n">name</span><span class="p">,</span> <span class="n">occupation</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="mf">199</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="n">name</span> <span class="k">AS</span> <span class="n">user_name</span><span class="p">,</span> <span class="n">occupation</span> <span class="k">AS</span> <span class="n">user_occupation</span> <span class="k">FROM</span> <span class="k">users</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="nb">time</span><span class="p">,</span> <span class="n">value</span>
<span class="k">FROM</span> <span class="n">events</span>
<span class="k">WHERE</span> <span class="n">event_type</span> <span class="o">=</span> <span class="s1">&#39;myEvent&#39;</span>
<span class="k">AND</span> <span class="nb">time</span> <span class="o">&gt;</span> <span class="s1">&#39;2011-02-03&#39;</span>
<span class="k">AND</span> <span class="nb">time</span> <span class="o">&lt;=</span> <span class="s1">&#39;2012-01-01&#39;</span>
<span class="k">SELECT</span> <span class="k">COUNT</span> <span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">user_count</span> <span class="k">FROM</span> <span class="k">users</span><span class="p">;</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">SELECT</span></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 class="reference internal" href="functions.html#cql-functions"><span class="std std-ref">functions</span></a> including <a class="reference internal" href="functions.html#aggregate-functions"><span class="std std-ref">aggregation</span></a> ones can be applied to the result.</p>
<p>A <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> statement contains at least a <a class="reference internal" href="#selection-clause"><span class="std std-ref">selection clause</span></a> and the name of the table on which
the selection is on (note that CQL does <strong>not</strong> joins or sub-queries and thus a select statement only apply to a single
table). In most case, a select will also have a <a class="reference internal" href="#where-clause"><span class="std std-ref">where clause</span></a> and it can optionally have additional
clauses to <a class="reference internal" href="#ordering-clause"><span class="std std-ref">order</span></a> or <a class="reference internal" href="#limit-clause"><span class="std std-ref">limit</span></a> the results. Lastly, <a class="reference internal" href="#allow-filtering"><span class="std std-ref">queries that require
filtering</span></a> can be allowed if the <code class="docutils literal notranslate"><span class="pre">ALLOW</span> <span class="pre">FILTERING</span></code> flag is provided.</p>
<div class="section" id="selection-clause">
<span id="id2"></span><h3>Selection clause<a class="headerlink" href="#selection-clause" title="Permalink to this headline"></a></h3>
<p>The <a class="reference internal" href="#grammar-token-select-clause"><code class="xref std std-token docutils literal notranslate"><span class="pre">select_clause</span></code></a> determines which columns needs to be queried and returned in the result-set, as well as any
transformation to apply to this result before returning. It consists of a comma-separated list of <em>selectors</em> or,
alternatively, of the wildcard character (<code class="docutils literal notranslate"><span class="pre">*</span></code>) to select all the columns defined in the table.</p>
<div class="section" id="selectors">
<h4>Selectors<a class="headerlink" href="#selectors" title="Permalink to this headline"></a></h4>
<p>A <a class="reference internal" href="#grammar-token-selector"><code class="xref std std-token docutils literal notranslate"><span class="pre">selector</span></code></a> can be one of:</p>
<ul class="simple">
<li>A column name of the table selected, to retrieve the values for that column.</li>
<li>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).</li>
<li>A casting, which allows to convert a nested selector to a (compatible) type.</li>
<li>A function call, where the arguments are selector themselves. See the section on <a class="reference internal" href="functions.html#cql-functions"><span class="std std-ref">functions</span></a> for
more details.</li>
<li>The special call <code class="docutils literal notranslate"><span class="pre">COUNT(*)</span></code> to the <a class="reference internal" href="functions.html#count-function"><span class="std std-ref">COUNT function</span></a>, which counts all non-null results.</li>
</ul>
</div>
<div class="section" id="aliases">
<h4>Aliases<a class="headerlink" href="#aliases" title="Permalink to this headline"></a></h4>
<p>Every <em>top-level</em> selector can also be aliased (using <cite>AS</cite>). If so, the name of the corresponding column in the result
set will be that of the alias. For instance:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="c1">// Without alias</span>
<span class="k">SELECT</span> <span class="n">intAsBlob</span><span class="p">(</span><span class="mf">4</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">;</span>
<span class="c1">// intAsBlob(4)</span>
<span class="c1">// --------------</span>
<span class="c1">// 0x00000004</span>
<span class="c1">// With alias</span>
<span class="k">SELECT</span> <span class="n">intAsBlob</span><span class="p">(</span><span class="mf">4</span><span class="p">)</span> <span class="k">AS</span> <span class="n">four</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">;</span>
<span class="c1">// four</span>
<span class="c1">// ------------</span>
<span class="c1">// 0x00000004</span>
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Currently, aliases aren’t recognized anywhere else in the statement where they are used (not in the <code class="docutils literal notranslate"><span class="pre">WHERE</span></code>
clause, not in the <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause, …). You must use the orignal column name instead.</p>
</div>
</div>
<div class="section" id="writetime-and-ttl-function">
<h4><code class="docutils literal notranslate"><span class="pre">WRITETIME</span></code> and <code class="docutils literal notranslate"><span class="pre">TTL</span></code> function<a class="headerlink" href="#writetime-and-ttl-function" title="Permalink to this headline"></a></h4>
<p>Selection supports two special functions (that aren’t allowed anywhere else): <code class="docutils literal notranslate"><span class="pre">WRITETIME</span></code> and <code class="docutils literal notranslate"><span class="pre">TTL</span></code>. Both function
take only one argument and that argument <em>must</em> be a column name (so for instance <code class="docutils literal notranslate"><span class="pre">TTL(3)</span></code> is invalid).</p>
<p>Those functions allow to retrieve meta-information that are stored internally for each column, namely:</p>
<ul class="simple">
<li>the timestamp of the value of the column for <code class="docutils literal notranslate"><span class="pre">WRITETIME</span></code>.</li>
<li>the remaining time to live (in seconds) for the value of the column if it set to expire (and <code class="docutils literal notranslate"><span class="pre">null</span></code> otherwise).</li>
</ul>
</div>
</div>
<div class="section" id="the-where-clause">
<span id="where-clause"></span><h3>The <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> clause<a class="headerlink" href="#the-where-clause" title="Permalink to this headline"></a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> clause specifies which rows must be queried. It is composed of relations on the columns that are part of
the <code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code> and/or have a <a class="reference external" href="#createIndexStmt">secondary index</a> defined on them.</p>
<p>Not all relations are allowed in a query. For instance, non-equal relations (where <code class="docutils literal notranslate"><span class="pre">IN</span></code> is considered as an equal
relation) on a partition key are not supported (but see the use of the <code class="docutils literal notranslate"><span class="pre">TOKEN</span></code> method below to do non-equal queries on
the partition key). Moreover, for a given partition key, the clustering columns induce an ordering of rows and relations
on them is restricted to the relations that allow to select a <strong>contiguous</strong> (for the ordering) set of rows. For
instance, given:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">posts</span> <span class="p">(</span>
<span class="n">userid</span> <span class="nb">text</span><span class="p">,</span>
<span class="n">blog_title</span> <span class="nb">text</span><span class="p">,</span>
<span class="n">posted_at</span> <span class="nb">timestamp</span><span class="p">,</span>
<span class="n">entry_title</span> <span class="nb">text</span><span class="p">,</span>
<span class="n">content</span> <span class="nb">text</span><span class="p">,</span>
<span class="n">category</span> <span class="nb">int</span><span class="p">,</span>
<span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">userid</span><span class="p">,</span> <span class="n">blog_title</span><span class="p">,</span> <span class="n">posted_at</span><span class="p">)</span>
<span class="p">)</span>
</pre></div>
</div>
<p>The following query is allowed:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">entry_title</span><span class="p">,</span> <span class="n">content</span> <span class="k">FROM</span> <span class="n">posts</span>
<span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">&#39;john doe&#39;</span>
<span class="k">AND</span> <span class="n">blog_title</span><span class="o">=</span><span class="s1">&#39;John&#39;&#39;s Blog&#39;</span>
<span class="k">AND</span> <span class="n">posted_at</span> <span class="o">&gt;=</span> <span class="s1">&#39;2012-01-01&#39;</span> <span class="k">AND</span> <span class="n">posted_at</span> <span class="o">&lt;</span> <span class="s1">&#39;2012-01-31&#39;</span>
</pre></div>
</div>
<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 class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="c1">// Needs a blog_title to be set to select ranges of posted_at</span>
<span class="k">SELECT</span> <span class="n">entry_title</span><span class="p">,</span> <span class="n">content</span> <span class="k">FROM</span> <span class="n">posts</span>
<span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">&#39;john doe&#39;</span>
<span class="k">AND</span> <span class="n">posted_at</span> <span class="o">&gt;=</span> <span class="s1">&#39;2012-01-01&#39;</span> <span class="k">AND</span> <span class="n">posted_at</span> <span class="o">&lt;</span> <span class="s1">&#39;2012-01-31&#39;</span>
</pre></div>
</div>
<p>When specifying relations, the <code class="docutils literal notranslate"><span class="pre">TOKEN</span></code> function can be used on the <code class="docutils literal notranslate"><span class="pre">PARTITION</span> <span class="pre">KEY</span></code> column to query. In that case,
rows will be selected based on the token of their <code class="docutils literal notranslate"><span class="pre">PARTITION_KEY</span></code> rather than on the value. Note that the token of a
key depends on the partitioner in use, and that in particular the RandomPartitioner won’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 class="docutils literal notranslate"><span class="pre">token(-1)</span> <span class="pre">&gt;</span> <span class="pre">token(0)</span></code> in particular). Example:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">posts</span>
<span class="k">WHERE</span> <span class="k">token</span><span class="p">(</span><span class="n">userid</span><span class="p">)</span> <span class="o">&gt;</span> <span class="k">token</span><span class="p">(</span><span class="s1">&#39;tom&#39;</span><span class="p">)</span> <span class="k">AND</span> <span class="k">token</span><span class="p">(</span><span class="n">userid</span><span class="p">)</span> <span class="o">&lt;</span> <span class="k">token</span><span class="p">(</span><span class="s1">&#39;bob&#39;</span><span class="p">)</span>
</pre></div>
</div>
<p>Moreover, the <code class="docutils literal notranslate"><span class="pre">IN</span></code> relation is only allowed on the last column of the partition key and on the last column of the full
primary key.</p>
<p>It is also possible to “group” <code class="docutils literal notranslate"><span class="pre">CLUSTERING</span> <span class="pre">COLUMNS</span></code> together in a relation using the tuple notation. For instance:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">posts</span>
<span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">&#39;john doe&#39;</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">blog_title</span><span class="p">,</span> <span class="n">posted_at</span><span class="p">)</span> <span class="o">&gt;</span> <span class="p">(</span><span class="s1">&#39;John&#39;&#39;s Blog&#39;</span><span class="p">,</span> <span class="s1">&#39;2012-01-01&#39;</span><span class="p">)</span>
</pre></div>
</div>
<p>will request all rows that sorts after the one having “John’s Blog” as <code class="docutils literal notranslate"><span class="pre">blog_tile</span></code> and ‘2012-01-01’ for <code class="docutils literal notranslate"><span class="pre">posted_at</span></code>
in the clustering order. In particular, rows having a <code class="docutils literal notranslate"><span class="pre">post_at</span> <span class="pre">&lt;=</span> <span class="pre">'2012-01-01'</span></code> will be returned as long as their
<code class="docutils literal notranslate"><span class="pre">blog_title</span> <span class="pre">&gt;</span> <span class="pre">'John''s</span> <span class="pre">Blog'</span></code>, which would not be the case for:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">posts</span>
<span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">&#39;john doe&#39;</span>
<span class="k">AND</span> <span class="n">blog_title</span> <span class="o">&gt;</span> <span class="s1">&#39;John&#39;&#39;s Blog&#39;</span>
<span class="k">AND</span> <span class="n">posted_at</span> <span class="o">&gt;</span> <span class="s1">&#39;2012-01-01&#39;</span>
</pre></div>
</div>
<p>The tuple notation may also be used for <code class="docutils literal notranslate"><span class="pre">IN</span></code> clauses on clustering columns:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">posts</span>
<span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">&#39;john doe&#39;</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">blog_title</span><span class="p">,</span> <span class="n">posted_at</span><span class="p">)</span> <span class="k">IN</span> <span class="p">((</span><span class="s1">&#39;John&#39;&#39;s Blog&#39;</span><span class="p">,</span> <span class="s1">&#39;2012-01-01&#39;</span><span class="p">),</span> <span class="p">(</span><span class="s1">&#39;Extreme Chess&#39;</span><span class="p">,</span> <span class="s1">&#39;2014-06-01&#39;</span><span class="p">))</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">CONTAINS</span></code> operator may only be used on collection columns (lists, sets, and maps). In the case of maps,
<code class="docutils literal notranslate"><span class="pre">CONTAINS</span></code> applies to the map values. The <code class="docutils literal notranslate"><span class="pre">CONTAINS</span> <span class="pre">KEY</span></code> operator may only be used on map columns and applies to the
map keys.</p>
</div>
<div class="section" id="grouping-results">
<span id="group-by-clause"></span><h3>Grouping results<a class="headerlink" href="#grouping-results" title="Permalink to this headline"></a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> option allows to condense into a single row all selected rows that share the same values for a set
of columns.</p>
<p>Using the <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> option, it is only possible to group rows at the partition key level or at a clustering column
level. By consequence, the <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> option only accept as arguments primary key column names in the primary key
order. If a primary key column is restricted by an equality restriction it is not required to be present in the
<code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause.</p>
<p>Aggregate functions will produce a separate value for each group. If no <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause is specified,
aggregates functions will produce a single value for all the rows.</p>
<p>If a column is selected without an aggregate function, in a statement with a <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code>, the first value encounter
in each group will be returned.</p>
</div>
<div class="section" id="ordering-results">
<span id="ordering-clause"></span><h3>Ordering results<a class="headerlink" href="#ordering-results" title="Permalink to this headline"></a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause allows to select the order of the returned results. It takes as argument a list of column names
along with the order for the column (<code class="docutils literal notranslate"><span class="pre">ASC</span></code> for ascendant and <code class="docutils literal notranslate"><span class="pre">DESC</span></code> for descendant, omitting the order being
equivalent to <code class="docutils literal notranslate"><span class="pre">ASC</span></code>). Currently the possible orderings are limited by the <a class="reference internal" href="ddl.html#clustering-order"><span class="std std-ref">clustering order</span></a>
defined on the table:</p>
<ul class="simple">
<li>if the table has been defined without any specific <code class="docutils literal notranslate"><span class="pre">CLUSTERING</span> <span class="pre">ORDER</span></code>, then then allowed orderings are the order
induced by the clustering columns and the reverse of that one.</li>
<li>otherwise, the orderings allowed are the order of the <code class="docutils literal notranslate"><span class="pre">CLUSTERING</span> <span class="pre">ORDER</span></code> option and the reversed one.</li>
</ul>
</div>
<div class="section" id="limiting-results">
<span id="limit-clause"></span><h3>Limiting results<a class="headerlink" href="#limiting-results" title="Permalink to this headline"></a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> option to a <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> statement limits the number of rows returned by a query, while the <code class="docutils literal notranslate"><span class="pre">PER</span> <span class="pre">PARTITION</span>
<span class="pre">LIMIT</span></code> option limits the number of rows returned for a given partition by the query. Note that both type of limit can
used in the same statement.</p>
</div>
<div class="section" id="allowing-filtering">
<span id="allow-filtering"></span><h3>Allowing filtering<a class="headerlink" href="#allowing-filtering" title="Permalink to this headline"></a></h3>
<p>By default, CQL only allows select queries that don’t involve “filtering” server side, i.e. queries where we know that
all (live) record read will be returned (maybe partly) in the result set. The reasoning is that those “non filtering”
queries have predictable performance in the sense that they will execute in a time that is proportional to the amount of
data <strong>returned</strong> by the query (which can be controlled through <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code>).</p>
<p>The <code class="docutils literal notranslate"><span class="pre">ALLOW</span> <span class="pre">FILTERING</span></code> option allows to explicitly allow (some) queries that require filtering. Please note that a
query using <code class="docutils literal notranslate"><span class="pre">ALLOW</span> <span class="pre">FILTERING</span></code> may thus have unpredictable performance (for the definition above), i.e. even a query
that selects a handful of records <strong>may</strong> exhibit performance that depends on the total amount of data stored in the
cluster.</p>
<p>For instance, considering the following table holding user profiles with their year of birth (with a secondary index on
it) and country of residence:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="k">users</span> <span class="p">(</span>
<span class="n">username</span> <span class="nb">text</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
<span class="n">firstname</span> <span class="nb">text</span><span class="p">,</span>
<span class="n">lastname</span> <span class="nb">text</span><span class="p">,</span>
<span class="n">birth_year</span> <span class="nb">int</span><span class="p">,</span>
<span class="n">country</span> <span class="nb">text</span>
<span class="p">)</span>
<span class="k">CREATE</span> <span class="k">INDEX</span> <span class="k">ON</span> <span class="k">users</span><span class="p">(</span><span class="n">birth_year</span><span class="p">);</span>
</pre></div>
</div>
<p>Then the following queries are valid:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">users</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">birth_year</span> <span class="o">=</span> <span class="mf">1981</span><span class="p">;</span>
</pre></div>
</div>
<p>because in both case, Cassandra guarantees that these queries performance will be proportional to the amount of data
returned. In particular, if no users are born in 1981, then the second query performance will not depend of the number
of user profile stored in the database (not directly at least: due to secondary index implementation consideration, this
query may still depend on the number of node in the cluster, which indirectly depends on the amount of data stored.
Nevertheless, the number of nodes will always be multiple number of magnitude lower than the number of user profile
stored). Of course, both query may return very large result set in practice, but the amount of data returned can always
be controlled by adding a <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code>.</p>
<p>However, the following query will be rejected:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">birth_year</span> <span class="o">=</span> <span class="mf">1981</span> <span class="k">AND</span> <span class="n">country</span> <span class="o">=</span> <span class="s1">&#39;FR&#39;</span><span class="p">;</span>
</pre></div>
</div>
<p>because Cassandra cannot guarantee that it won’t have to scan large amount of data even if the result to those query is
small. Typically, it will scan all the index entries for users born in 1981 even if only a handful are actually from
France. However, if you “know what you are doing”, you can force the execution of this query by using <code class="docutils literal notranslate"><span class="pre">ALLOW</span>
<span class="pre">FILTERING</span></code> and so the following query is valid:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">birth_year</span> <span class="o">=</span> <span class="mf">1981</span> <span class="k">AND</span> <span class="n">country</span> <span class="o">=</span> <span class="s1">&#39;FR&#39;</span> <span class="k">ALLOW</span> <span class="k">FILTERING</span><span class="p">;</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="insert">
<span id="insert-statement"></span><h2>INSERT<a class="headerlink" href="#insert" title="Permalink to this headline"></a></h2>
<p>Inserting data for a row is done using an <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> statement:</p>
<pre>
<strong id="grammar-token-insert-statement">insert_statement</strong> ::= INSERT INTO <a class="reference internal" href="ddl.html#grammar-token-table-name"><code class="xref docutils literal notranslate"><span class="pre">table_name</span></code></a> ( <a class="reference internal" href="#grammar-token-names-values"><code class="xref docutils literal notranslate"><span class="pre">names_values</span></code></a> | <a class="reference internal" href="#grammar-token-json-clause"><code class="xref docutils literal notranslate"><span class="pre">json_clause</span></code></a> )
[ IF NOT EXISTS ]
[ USING <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> )* ]
<strong id="grammar-token-names-values">names_values </strong> ::= <a class="reference internal" href="#grammar-token-names"><code class="xref docutils literal notranslate"><span class="pre">names</span></code></a> VALUES <a class="reference internal" href="types.html#grammar-token-tuple-literal"><code class="xref docutils literal notranslate"><span class="pre">tuple_literal</span></code></a>
<strong id="grammar-token-json-clause">json_clause </strong> ::= JSON <a class="reference internal" href="definitions.html#grammar-token-string"><code class="xref docutils literal notranslate"><span class="pre">string</span></code></a> [ DEFAULT ( NULL | UNSET ) ]
<strong id="grammar-token-names">names </strong> ::= '(' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* ')'
</pre>
<p>For instance:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">NerdMovies</span> <span class="p">(</span><span class="n">movie</span><span class="p">,</span> <span class="n">director</span><span class="p">,</span> <span class="n">main_actor</span><span class="p">,</span> <span class="n">year</span><span class="p">)</span>
<span class="k">VALUES</span> <span class="p">(</span><span class="s1">&#39;Serenity&#39;</span><span class="p">,</span> <span class="s1">&#39;Joss Whedon&#39;</span><span class="p">,</span> <span class="s1">&#39;Nathan Fillion&#39;</span><span class="p">,</span> <span class="mf">2005</span><span class="p">)</span>
<span class="k">USING</span> <span class="k">TTL</span> <span class="mf">86400</span><span class="p">;</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">NerdMovies</span> <span class="k">JSON</span> <span class="s1">&#39;{&quot;movie&quot;: &quot;Serenity&quot;,</span>
<span class="s1"> &quot;director&quot;: &quot;Joss Whedon&quot;,</span>
<span class="s1"> &quot;year&quot;: 2005}&#39;</span><span class="p">;</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> statement writes one or more columns for a given row in a table. Note that since a row is identified by
its <code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code>, at least the columns composing it must be specified. The list of columns to insert to must be
supplied when using the <code class="docutils literal notranslate"><span class="pre">VALUES</span></code> syntax. When using the <code class="docutils literal notranslate"><span class="pre">JSON</span></code> syntax, they are optional. See the
section on <a class="reference internal" href="json.html#cql-json"><span class="std std-ref">JSON support</span></a> for more detail.</p>
<p>Note that unlike in SQL, <code class="docutils literal notranslate"><span class="pre">INSERT</span></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 mean to know which of creation or update happened.</p>
<p>It is however possible to use the <code class="docutils literal notranslate"><span class="pre">IF</span> <span class="pre">NOT</span> <span class="pre">EXISTS</span></code> condition to only insert if the row does not exist prior to the
insertion. But please note that using <code class="docutils literal notranslate"><span class="pre">IF</span> <span class="pre">NOT</span> <span class="pre">EXISTS</span></code> will incur a non negligible performance cost (internally, Paxos
will be used) so this should be used sparingly.</p>
<p>All updates for an <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> are applied atomically and in isolation.</p>
<p>Please refer to the <a class="reference internal" href="#update-parameters"><span class="std std-ref">UPDATE</span></a> section for informations on the <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref std std-token docutils literal notranslate"><span class="pre">update_parameter</span></code></a>.</p>
<p>Also note that <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> does not support counters, while <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> does.</p>
</div>
<div class="section" id="update">
<span id="update-statement"></span><h2>UPDATE<a class="headerlink" href="#update" title="Permalink to this headline"></a></h2>
<p>Updating a row is done using an <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> statement:</p>
<pre>
<strong id="grammar-token-update-statement">update_statement</strong> ::= UPDATE <a class="reference internal" href="ddl.html#grammar-token-table-name"><code class="xref docutils literal notranslate"><span class="pre">table_name</span></code></a>
[ USING <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> )* ]
SET <a class="reference internal" href="#grammar-token-assignment"><code class="xref docutils literal notranslate"><span class="pre">assignment</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-assignment"><code class="xref docutils literal notranslate"><span class="pre">assignment</span></code></a> )*
WHERE <a class="reference internal" href="#grammar-token-where-clause"><code class="xref docutils literal notranslate"><span class="pre">where_clause</span></code></a>
[ IF ( EXISTS | <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal notranslate"><span class="pre">condition</span></code></a> ( AND <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal notranslate"><span class="pre">condition</span></code></a> )*) ]
<strong id="grammar-token-update-parameter">update_parameter</strong> ::= ( TIMESTAMP | TTL ) ( <a class="reference internal" href="definitions.html#grammar-token-integer"><code class="xref docutils literal notranslate"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind-marker"><code class="xref docutils literal notranslate"><span class="pre">bind_marker</span></code></a> )
<strong id="grammar-token-assignment">assignment </strong> ::= <a class="reference internal" href="#grammar-token-simple-selection"><code class="xref docutils literal notranslate"><span class="pre">simple_selection</span></code></a> '=' <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a>
| <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> '=' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> ( '+' | '-' ) <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a>
| <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> '=' <a class="reference internal" href="types.html#grammar-token-list-literal"><code class="xref docutils literal notranslate"><span class="pre">list_literal</span></code></a> '+' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a>
<strong id="grammar-token-simple-selection">simple_selection</strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a>
| <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> '[' <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a> ']'
| <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> '.' `field_name
<strong id="grammar-token-condition">condition </strong> ::= <a class="reference internal" href="#grammar-token-simple-selection"><code class="xref docutils literal notranslate"><span class="pre">simple_selection</span></code></a> <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal notranslate"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a>
</pre>
<p>For instance:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">UPDATE</span> <span class="n">NerdMovies</span> <span class="k">USING</span> <span class="k">TTL</span> <span class="mf">400</span>
<span class="k">SET</span> <span class="n">director</span> <span class="o">=</span> <span class="s1">&#39;Joss Whedon&#39;</span><span class="p">,</span>
<span class="n">main_actor</span> <span class="o">=</span> <span class="s1">&#39;Nathan Fillion&#39;</span><span class="p">,</span>
<span class="n">year</span> <span class="o">=</span> <span class="mf">2005</span>
<span class="k">WHERE</span> <span class="n">movie</span> <span class="o">=</span> <span class="s1">&#39;Serenity&#39;</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">UserActions</span>
<span class="k">SET</span> <span class="n">total</span> <span class="o">=</span> <span class="n">total</span> <span class="o">+</span> <span class="mf">2</span>
<span class="k">WHERE</span> <span class="k">user</span> <span class="o">=</span> <span class="m">B70DE1D0-9908-4AE3-BE34-5573E5B09F14</span>
<span class="k">AND</span> <span class="n">action</span> <span class="o">=</span> <span class="s1">&#39;click&#39;</span><span class="p">;</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> statement writes one or more columns for a given row in a table. The <a class="reference internal" href="#grammar-token-where-clause"><code class="xref std std-token docutils literal notranslate"><span class="pre">where_clause</span></code></a> is used to
select the row to update and must include all columns composing the <code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code>. Non primary key columns are then
set using the <code class="docutils literal notranslate"><span class="pre">SET</span></code> keyword.</p>
<p>Note that unlike in SQL, <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> does not check the prior existence of the row by default (except through <code class="docutils literal notranslate"><span class="pre">IF</span></code>, see
below): the row is created if none existed before, and updated otherwise. Furthermore, there are no means to know
whether a creation or update occurred.</p>
<p>It is however possible to use the conditions on some columns through <code class="docutils literal notranslate"><span class="pre">IF</span></code>, in which case the row will not be updated
unless the conditions are met. But, please note that using <code class="docutils literal notranslate"><span class="pre">IF</span></code> conditions will incur a non-negligible performance
cost (internally, Paxos will be used) so this should be used sparingly.</p>
<p>In an <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> statement, all updates within the same partition key are applied atomically and in isolation.</p>
<p>Regarding the <a class="reference internal" href="#grammar-token-assignment"><code class="xref std std-token docutils literal notranslate"><span class="pre">assignment</span></code></a>:</p>
<ul class="simple">
<li><code class="docutils literal notranslate"><span class="pre">c</span> <span class="pre">=</span> <span class="pre">c</span> <span class="pre">+</span> <span class="pre">3</span></code> is used to increment/decrement counters. The column name after the ‘=’ sign <strong>must</strong> be the same than
the one before the ‘=’ sign. Note that increment/decrement is only allowed on counters, and are the <em>only</em> update
operations allowed on counters. See the section on <a class="reference internal" href="types.html#counters"><span class="std std-ref">counters</span></a> for details.</li>
<li><code class="docutils literal notranslate"><span class="pre">id</span> <span class="pre">=</span> <span class="pre">id</span> <span class="pre">+</span> <span class="pre">&lt;some-collection&gt;</span></code> and <code class="docutils literal notranslate"><span class="pre">id[value1]</span> <span class="pre">=</span> <span class="pre">value2</span></code> are for collections, see the <a class="reference internal" href="types.html#collections"><span class="std std-ref">relevant section</span></a> for details.</li>
<li><code class="docutils literal notranslate"><span class="pre">id.field</span> <span class="pre">=</span> <span class="pre">3</span></code> is for setting the value of a field on a non-frozen user-defined types. see the <a class="reference internal" href="types.html#udts"><span class="std std-ref">relevant section</span></a> for details.</li>
</ul>
<div class="section" id="update-parameters">
<span id="id3"></span><h3>Update parameters<a class="headerlink" href="#update-parameters" title="Permalink to this headline"></a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> (and <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> and <code class="docutils literal notranslate"><span class="pre">BATCH</span></code> for the <code class="docutils literal notranslate"><span class="pre">TIMESTAMP</span></code>) statements support the following
parameters:</p>
<ul class="simple">
<li><code class="docutils literal notranslate"><span class="pre">TIMESTAMP</span></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.</li>
<li><code class="docutils literal notranslate"><span class="pre">TTL</span></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 class="docutils literal notranslate"><span class="pre">null</span></code> is equivalent
to inserting with a TTL of 0.</li>
</ul>
</div>
</div>
<div class="section" id="delete">
<span id="delete-statement"></span><h2>DELETE<a class="headerlink" href="#delete" title="Permalink to this headline"></a></h2>
<p>Deleting rows or parts of rows uses the <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> statement:</p>
<pre>
<strong id="grammar-token-delete-statement">delete_statement</strong> ::= DELETE [ <a class="reference internal" href="#grammar-token-simple-selection"><code class="xref docutils literal notranslate"><span class="pre">simple_selection</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-simple-selection"><code class="xref docutils literal notranslate"><span class="pre">simple_selection</span></code></a> ) ]
FROM <a class="reference internal" href="ddl.html#grammar-token-table-name"><code class="xref docutils literal notranslate"><span class="pre">table_name</span></code></a>
[ USING <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> )* ]
WHERE <a class="reference internal" href="#grammar-token-where-clause"><code class="xref docutils literal notranslate"><span class="pre">where_clause</span></code></a>
[ IF ( EXISTS | <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal notranslate"><span class="pre">condition</span></code></a> ( AND <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal notranslate"><span class="pre">condition</span></code></a> )*) ]
</pre>
<p>For instance:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">NerdMovies</span> <span class="k">USING</span> <span class="nb">TIMESTAMP</span> <span class="mf">1240003134</span>
<span class="k">WHERE</span> <span class="n">movie</span> <span class="o">=</span> <span class="s1">&#39;Serenity&#39;</span><span class="p">;</span>
<span class="k">DELETE</span> <span class="n">phone</span> <span class="k">FROM</span> <span class="k">Users</span>
<span class="k">WHERE</span> <span class="n">userid</span> <span class="k">IN</span> <span class="p">(</span><span class="m">C73DE1D3-AF08-40F3-B124-3FF3E5109F22</span><span class="p">,</span> <span class="m">B70DE1D0-9908-4AE3-BE34-5573E5B09F14</span><span class="p">);</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> statement deletes columns and rows. If column names are provided directly after the <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> keyword,
only those columns are deleted from the row indicated by the <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> clause. Otherwise, whole rows are removed.</p>
<p>The <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> clause specifies which rows are to be deleted. Multiple rows may be deleted with one statement by using an
<code class="docutils literal notranslate"><span class="pre">IN</span></code> operator. A range of rows may be deleted using an inequality operator (such as <code class="docutils literal notranslate"><span class="pre">&gt;=</span></code>).</p>
<p><code class="docutils literal notranslate"><span class="pre">DELETE</span></code> supports the <code class="docutils literal notranslate"><span class="pre">TIMESTAMP</span></code> option with the same semantics as in <a class="reference internal" href="#update-parameters"><span class="std std-ref">updates</span></a>.</p>
<p>In a <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> statement, all deletions within the same partition key are applied atomically and in isolation.</p>
<p>A <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> operation can be conditional through the use of an <code class="docutils literal notranslate"><span class="pre">IF</span></code> clause, similar to <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> and <code class="docutils literal notranslate"><span class="pre">INSERT</span></code>
statements. However, as with <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> and <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> statements, this will incur a non-negligible performance cost
(internally, Paxos will be used) and so should be used sparingly.</p>
</div>
<div class="section" id="batch">
<span id="batch-statement"></span><h2>BATCH<a class="headerlink" href="#batch" title="Permalink to this headline"></a></h2>
<p>Multiple <code class="docutils literal notranslate"><span class="pre">INSERT</span></code>, <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> and <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> can be executed in a single statement by grouping them through a
<code class="docutils literal notranslate"><span class="pre">BATCH</span></code> statement:</p>
<pre>
<strong id="grammar-token-batch-statement">batch_statement </strong> ::= BEGIN [ UNLOGGED | COUNTER ] BATCH
[ USING <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> )* ]
<a class="reference internal" href="#grammar-token-modification-statement"><code class="xref docutils literal notranslate"><span class="pre">modification_statement</span></code></a> ( ';' <a class="reference internal" href="#grammar-token-modification-statement"><code class="xref docutils literal notranslate"><span class="pre">modification_statement</span></code></a> )*
APPLY BATCH
<strong id="grammar-token-modification-statement">modification_statement</strong> ::= <a class="reference internal" href="#grammar-token-insert-statement"><code class="xref docutils literal notranslate"><span class="pre">insert_statement</span></code></a> | <a class="reference internal" href="#grammar-token-update-statement"><code class="xref docutils literal notranslate"><span class="pre">update_statement</span></code></a> | <a class="reference internal" href="#grammar-token-delete-statement"><code class="xref docutils literal notranslate"><span class="pre">delete_statement</span></code></a>
</pre>
<p>For instance:</p>
<div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">BEGIN</span> <span class="k">BATCH</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="k">users</span> <span class="p">(</span><span class="n">userid</span><span class="p">,</span> <span class="k">password</span><span class="p">,</span> <span class="n">name</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="s1">&#39;user2&#39;</span><span class="p">,</span> <span class="s1">&#39;ch@ngem3b&#39;</span><span class="p">,</span> <span class="s1">&#39;second user&#39;</span><span class="p">);</span>
<span class="k">UPDATE</span> <span class="k">users</span> <span class="k">SET</span> <span class="k">password</span> <span class="o">=</span> <span class="s1">&#39;ps22dhds&#39;</span> <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">&#39;user3&#39;</span><span class="p">;</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="k">users</span> <span class="p">(</span><span class="n">userid</span><span class="p">,</span> <span class="k">password</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="s1">&#39;user4&#39;</span><span class="p">,</span> <span class="s1">&#39;ch@ngem3c&#39;</span><span class="p">);</span>
<span class="k">DELETE</span> <span class="n">name</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">&#39;user1&#39;</span><span class="p">;</span>
<span class="k">APPLY</span> <span class="k">BATCH</span><span class="p">;</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">BATCH</span></code> statement group multiple modification statements (insertions/updates and deletions) into a single
statement. It serves several purposes:</p>
<ul class="simple">
<li>It saves network round-trips between the client and the server (and sometimes between the server coordinator and the
replicas) when batching multiple updates.</li>
<li>All updates in a <code class="docutils literal notranslate"><span class="pre">BATCH</span></code> belonging to a given partition key are performed in isolation.</li>
<li>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 class="reference internal" href="#unlogged-batches"><span class="std std-ref">UNLOGGED batches</span></a> for more details.</li>
</ul>
<p>Note that:</p>
<ul class="simple">
<li><code class="docutils literal notranslate"><span class="pre">BATCH</span></code> statements may only contain <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> and <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> statements (not other batches for instance).</li>
<li>Batches are <em>not</em> a full analogue for SQL transactions.</li>
<li>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’s conflict
resolution procedure in the case of <a class="reference external" 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 class="docutils literal notranslate"><span class="pre">BATCH</span></code> statement. To force a
particular operation ordering, you must specify per-operation timestamps.</li>
<li>A LOGGED batch to a single partition will be converted to an UNLOGGED batch as an optimization.</li>
</ul>
<div class="section" id="unlogged-batches">
<span id="id4"></span><h3><code class="docutils literal notranslate"><span class="pre">UNLOGGED</span></code> batches<a class="headerlink" href="#unlogged-batches" title="Permalink to this headline"></a></h3>
<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>
<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 class="docutils literal notranslate"><span class="pre">UNLOGGED</span></code> option. If the <code class="docutils literal notranslate"><span class="pre">UNLOGGED</span></code> option is
used, a failed batch might leave the patch only partly applied.</p>
</div>
<div class="section" id="counter-batches">
<h3><code class="docutils literal notranslate"><span class="pre">COUNTER</span></code> batches<a class="headerlink" href="#counter-batches" title="Permalink to this headline"></a></h3>
<p>Use the <code class="docutils literal notranslate"><span class="pre">COUNTER</span></code> option for batched counter updates. Unlike other
updates in Cassandra, counter updates are not idempotent.</p>
</div>
</div>
</div>
<div class="doc-prev-next-links" role="navigation" aria-label="footer navigation">
<a href="indexes.html" class="btn btn-default pull-right " role="button" title="Secondary Indexes" accesskey="n">Next <span class="glyphicon glyphicon-circle-arrow-right" aria-hidden="true"></span></a>
<a href="ddl.html" class="btn btn-default" role="button" title="Data Definition" accesskey="p"><span class="glyphicon glyphicon-circle-arrow-left" aria-hidden="true"></span> Previous</a>
</div>
</div>
</div>
</div>
</div>
</div>
<hr />
<footer>
<div class="container">
<div class="col-md-4 social-blk">
<span class="social">
<a href="https://twitter.com/cassandra"
class="twitter-follow-button"
data-show-count="false" data-size="large">Follow @cassandra</a>
<script>!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+'://platform.twitter.com/widgets.js';fjs.parentNode.insertBefore(js,fjs);}}(document, 'script', 'twitter-wjs');</script>
<a href="https://twitter.com/intent/tweet?button_hashtag=cassandra"
class="twitter-hashtag-button"
data-size="large"
data-related="ApacheCassandra">Tweet #cassandra</a>
<script>!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+'://platform.twitter.com/widgets.js';fjs.parentNode.insertBefore(js,fjs);}}(document, 'script', 'twitter-wjs');</script>
</span>
<a class="subscribe-rss icon-link" href="/feed.xml" title="Subscribe to Blog via RSS">
<span><i class="fa fa-rss"></i></span>
</a>
</div>
<div class="col-md-8 trademark">
<p>&copy; 2016 <a href="http://apache.org">The Apache Software Foundation</a>.
Apache, the Apache feather logo, and Apache Cassandra are trademarks of The Apache Software Foundation.
<p>
</div>
</div><!-- /.container -->
</footer>
<!-- Javascript. Placed here so pages load faster -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script src="./../../../js/underscore-min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js" integrity="sha384-0mSbJDEHialfmuBBQP6A4Qrprq5OVfW37PRR3j5ELqxss1yVqOtnepnHVP9aJ7xS" crossorigin="anonymous"></script>
<script src="./../../../js/doctools.js"></script>
<script src="./../../../js/searchtools.js"></script>
<script type="text/javascript"> var DOCUMENTATION_OPTIONS = { URL_ROOT: "", VERSION: "", COLLAPSE_INDEX: false, FILE_SUFFIX: ".html", HAS_SOURCE: false, SOURCELINK_SUFFIX: ".txt" }; </script>
<script type="text/javascript">
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
try {
var pageTracker = _gat._getTracker("UA-11583863-1");
pageTracker._trackPageview();
} catch(err) {}
</script>
</body>
</html>