Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.0 (latest version) v1.3 (earlier version) v1.2 (earlier version) v1.1 (earlier version) v1.0 (earlier version)
  • Introduction
  • Quick Start
    • 1. Install YugabyteDB
    • 2. Create Local Cluster
    • 3. Test YCQL API
    • 4. Test YEDIS API
    • 5. Test YSQL API
    • 6. Run Sample Apps
  • Explore Core Features
    • Cloud Native
      • 1. Linear Scalability
      • 2. Fault Tolerance
      • 3. Observability
      • 4. Orchestration Readiness
    • Transactional
      • 1. ACID Transactions
      • 2. Secondary Indexes
      • 3. JSON Documents
    • High Performance
      • Tunable Reads
    • Planet Scale
      • 1. Global Distribution
      • 2. Auto Sharding
      • 3. Auto Rebalancing
    • PostgreSQL (Beta)
      • 1. Linear Scalability
      • 2. Fault Tolerance
      • 3. JOINs
      • 4. Aggregations
      • 5. Expressions
      • 6. Views
  • Develop
    • Tools
      • cqlsh
      • TablePlus
    • Client Drivers
      • C/C++
      • C#
      • Go
      • Java
      • NodeJS
      • Python
    • Learn App Dev
      • 1. SQL vs NoSQL
      • 2. Data Modeling
      • 3. Data Types
      • 4. ACID Transactions
      • 5. Aggregations
      • 6. Batch Operations
    • Ecosystem Integrations
      • Apache Kafka
      • Apache Spark
      • JanusGraph
      • KairosDB
      • Presto
      • Metabase
    • Real World Examples
      • E-Commerce App
      • IoT Fleet Management
      • Retail Analytics
  • Deploy
    • Checklist
    • Manual Deployment
      • 1. System Configuration
      • 2. Install Software
      • 3. Start YB-Masters
      • 4. Start YB-TServers
      • 5. Verify Deployment
    • Kubernetes
      • Helm Chart
      • Local SSD
    • Docker Swarm
    • Public Clouds
      • Amazon Web Services
      • Google Cloud Platform
      • Microsoft Azure
    • Pivotal Cloud Foundry
    • Enterprise Edition
      • 1. Prepare Cloud Env
      • 2. Install Admin Console
      • 3. Configure Admin Console
      • 4. Configure Cloud Providers
  • Secure
    • Security Checklist
    • Authentication
    • Authorization
      • 1. RBAC Model
      • 2. Create Roles
      • 3. Grant Permissions
    • TLS Encryption
      • 1. Prepare Nodes
      • 2. Server-Server Encryption
      • 3. Client-Server Encryption
      • 4. Connect to Cluster
  • Manage
    • Backup and Restore
      • Backing Up Data
      • Restoring Data
    • Data Migration
      • Bulk Import
      • Bulk Export
    • Change Cluster Config
    • Upgrade Deployment
    • Diagnostics Reporting
    • Enterprise Edition
      • Create Universe - Multi-Zone
      • Create Universe - Multi-Region
      • Edit Universe
      • Edit Config Flags
      • Health Checking and Alerts
      • Node Status & Actions
      • Read Replicas
      • Backup & Restore
      • Upgrade Universe
      • Delete Universe
  • Troubleshoot
    • Troubleshooting Overview
    • Cluster Level Issues
      • YCQL Connection Issues
      • YEDIS Connection Issues
    • Node Level Issues
      • Check Processes
      • Inspect Logs
      • System Stats
    • Enterprise Edition
      • Troubleshoot Universes
  • Architecture
    • Basic Concepts
      • Key Components
      • YQL Query Layer
      • DocDB Document Store
        • Sharding
        • Replication
        • Persistence
      • Acknowledgements
    • Transactions
      • Isolation Levels
      • Single Row Transactions
      • Distributed Transactions
      • Transactional IO Path
  • Comparisons
    • Apache Cassandra
    • MongoDB
    • Redis In-Memory Store
    • FoundationDB
    • Amazon DynamoDB
    • Azure Cosmos DB
    • Google Cloud Spanner
    • Apache HBase
  • API Reference
    • YCQL
      • ALTER KEYSPACE
      • ALTER ROLE
      • ALTER TABLE
      • CREATE INDEX
      • CREATE KEYSPACE
      • CREATE ROLE
      • CREATE TABLE
      • CREATE TYPE
      • DROP INDEX
      • DROP KEYSPACE
      • DROP ROLE
      • DROP TABLE
      • DROP TYPE
      • GRANT PERMISSION
      • GRANT ROLE
      • REVOKE PERMISSION
      • REVOKE ROLE
      • USE
      • INSERT
      • SELECT
      • UPDATE
      • DELETE
      • TRANSACTION
      • TRUNCATE
      • Simple Value
      • Subscript
      • Function Call
      • Operator Call
      • BLOB
      • BOOLEAN
      • MAP, SET, LIST
      • FROZEN
      • INET
      • Integer & Counter
      • Non-Integer
      • TEXT
      • Date & Time Types
      • UUID & TIMEUUID
      • JSONB
      • Date & Time Functions
    • YEDIS
      • APPEND
      • AUTH
      • CONFIG
      • CREATEDB
      • DELETEDB
      • LISTDB
      • SELECT
      • DEL
      • ECHO
      • EXISTS
      • EXPIRE
      • EXPIREAT
      • FLUSHALL
      • FLUSHDB
      • GET
      • GETRANGE
      • GETSET
      • HDEL
      • HEXISTS
      • HGET
      • HGETALL
      • HINCRBY
      • HKEYS
      • HLEN
      • HMGET
      • HMSET
      • HSET
      • HSTRLEN
      • HVALS
      • INCR
      • INCRBY
      • KEYS
      • MONITOR
      • PEXPIRE
      • PEXPIREAT
      • PTTL
      • ROLE
      • SADD
      • SCARD
      • SET
      • SETRANGE
      • SISMEMBER
      • SMEMBERS
      • SREM
      • STRLEN
      • ZRANGE
      • TSADD
      • TSCARD
      • TSGET
      • TSLASTN
      • TSRANGEBYTIME
      • TSREM
      • TSREVRANGEBYTIME
      • TTL
      • ZADD
      • ZCARD
      • ZRANGEBYSCORE
      • ZREM
      • ZREVRANGE
      • PUBSUB
      • PUBLISH
      • SUBSCRIBE
      • UNSUBSCRIBE
      • PSUBSCRIBE
      • PUNSUBSCRIBE
    • YSQL (Beta)
      • DDL Statements
        • CREATE DATABASE
        • CREATE TABLE
        • CREATE VIEW
        • DROP DATABASE
        • DROP TABLE
      • DML Statements
        • INSERT
        • SELECT
      • Datatypes
        • FLOAT
        • INTEGER
        • TEXT
      • Transactions
      • Roles and Permissions
      • Prepared Statements
      • Explain Statement
  • Admin Reference
    • yb-ctl
    • yb-docker-ctl
    • docker-compose
    • yb-master
    • yb-tserver
  • FAQs
    • Product
    • Architecture
    • Enterprise Edition
    • Cassandra Compatibility
> API Reference > YCQL >

DELETE

Attention

This page documents an earlier version. Go to the latest (v2.0)version.

    • Synopsis
    • Syntax
      • Diagram
      • Grammar
    • Semantics
      • WHERE Clause
      • IF Clause
      • USING Clause
    • Examples
      • Delete a row from a table
      • Conditional delete using the IF clause
      • Delete several rows with the same partition key
      • Delete with the USING TIMESTAMP clause
    • See Also

Synopsis

The DELETE statement removes rows from a specified table that meet a given condition. Currently, Yugabyte can deletes one row at a time. Deleting multiple rows is not yet supported.

Syntax

Diagram

DELETEFROMtable_nameUSINGTIMESTAMPtimestamp_expressionWHEREwhere_expressionIFNOTEXISTSif_expression

Grammar

delete ::= DELETE FROM table_name
               [ USING TIMESTAMP timestamp_expression ]
               WHERE where_expression
               [ IF { [ NOT ] EXISTS | if_expression } ];

Where

  • table_name is an identifier (possibly qualified with a keyspace name).
  • Restrictions on where_expression and if_expression are covered in the Semantics section below.
  • See Expressions for more information on syntax rules.

Semantics

  • An error is raised if the specified table_name does not exist.
  • The where_expression and if_expression must evaluate to boolean values.
  • The USING TIMESTAMP clause indicates we would like to perform the DELETE as if it was done at the timestamp provided by the user. The timestamp is the number of microseconds since epoch.
  • NOTE: You should either use the USING TIMESTAMP clause in all of your statements or none of them. Using a mix of statements where some have USING TIMESTAMP and others do not will lead to very confusing results.

WHERE Clause

  • The where_expression must specify conditions for all primary-key columns.
  • The where_expression must not specify conditions for any regular columns.
  • The where_expression can only apply AND and = operators. Other operators are not yet supported.

IF Clause

  • The if_expression can only apply to non-key columns (regular columns).
  • The if_expression can contain any logical and boolean operators.
  • Deleting only some column values from a row is not yet supported.
  • IF EXISTS and IF NOT EXISTS options are mostly for symmetry with the INSERT and UPDATE statements
    • IF EXISTS works like a normal delete but additionally returns whether the delete was applied (a row was found with that primary key).
    • IF NOT EXISTS is effectively a no-op since rows that do not exist cannot be deleted (but returns whether no row was found with that primary key).

USING Clause

  • timestamp_expression must be an integer value (or a bind variable marker for prepared statements).

Examples

Delete a row from a table

cqlsh:example> CREATE TABLE employees(department_id INT, 
                                      employee_id INT, 
                                      name TEXT, 
                                      PRIMARY KEY(department_id, employee_id));
cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John');
cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 2, 'Jane');
cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Joe');
cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane
             2 |           1 |  Joe

Delete statements identify rows by the primary key columns.

cqlsh:example> DELETE FROM employees WHERE department_id = 1 AND employee_id = 1;

Deletes on non-existent rows are no-ops.

cqlsh:example> DELETE FROM employees WHERE department_id = 3 AND employee_id = 1;
cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             1 |           2 | Jane
             2 |           1 |  Joe

Conditional delete using the IF clause

‘IF’ clause conditions will return whether they were applied or not.

cqlsh:example> DELETE FROM employees WHERE department_id = 2 AND employee_id = 1 IF name = 'Joe';
 [applied]
-----------
      True
cqlsh:example> DELETE FROM employees WHERE department_id = 3 AND employee_id = 1 IF EXISTS;
 [applied]
-----------
     False
cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             1 |           2 | Jane

Delete several rows with the same partition key

cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John');
cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Joe');
cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 2, 'Jack');
cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane
             2 |           1 |  Joe
             2 |           2 | Jack

Delete all entries for a partition key.

cqlsh:example> DELETE FROM employees WHERE department_id = 1;
cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe
             2 |           2 | Jack

Delete a range of entries within a partition key.

cqlsh:example> DELETE FROM employees WHERE department_id = 2 AND employee_id >= 2 AND employee_id < 4;
cqlsh:example> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe

Delete with the USING TIMESTAMP clause

You can do this as shown below.

cqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (4, 4, 'Ted') USING TIMESTAMP 1000;
cqlsh:foo> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             4 |           4 |  Ted
             2 |           1 |  Joe

(2 rows)
cqlsh:foo> DELETE FROM employees USING TIMESTAMP 500 WHERE department_id = 4 AND employee_id = 4; 

Not applied since timestamp is lower than 1000

cqlsh:foo> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             4 |           4 |  Ted
             2 |           1 |  Joe

(2 rows)
cqlsh:foo> DELETE FROM employees USING TIMESTAMP 1500 WHERE department_id = 4 AND employee_id = 4; 

Applied since timestamp is higher than 1000.

cqlsh:foo> SELECT * FROM employees;
 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe

(1 rows)

See Also

CREATE TABLE INSERT SELECT UPDATE TRUNCATE Expression Other CQL Statements

    • Synopsis
    • Syntax
      • Diagram
      • Grammar
    • Semantics
      • WHERE Clause
      • IF Clause
      • USING Clause
    • Examples
      • Delete a row from a table
      • Conditional delete using the IF clause
      • Delete several rows with the same partition key
      • Delete with the USING TIMESTAMP clause
    • See Also
API Reference
UPDATE
API Reference
TRANSACTION
Talk to Community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2019 Yugabyte, Inc. All rights reserved.