Section 8: Data Operations

Master UPDATE Operations

Complete guide to updating data in Cassandra - SET operations, counters, collections, TTL, conditional updates, and lightweight transactions!

Home › Section 8: Data Operations › UPDATE Data

🎯 UPDATE in Cassandra

CRITICAL: UPDATE is Actually UPSERT!

In Cassandra, UPDATE and INSERT are the SAME operation! UPDATE doesn't check if row exists - it will CREATE if missing (UPSERT)!

💡 Key Insight:

UPDATE = INSERT if not exists, MODIFY if exists
No error if row missing → Just creates it!

✅ What UPDATE Does

  • ✓ Set column values
  • ✓ Update collections (append/remove)
  • ✓ Increment/decrement counters
  • ✓ Set TTL on columns
  • ✓ UPSERT (create if missing)
  • ✓ Conditional updates (IF)

❌ What UPDATE Cannot Do

  • ✗ Update PRIMARY KEY columns
  • ✗ Partial collection updates (some)
  • ✗ Transactions across partitions
  • ✗ Read-modify-write atomically
  • ✗ Return old values
  • ✗ JOIN updates

📖 Basic UPDATE Syntax

Complete UPDATE Syntax

Full Syntax
UPDATE table_name
[USING TTL seconds | TIMESTAMP microseconds]
SET column1 = value1,
    column2 = value2,
    ...
WHERE primary_key_column = value
  AND clustering_column = value
[IF condition];

-- Counter updates
UPDATE table_name
SET counter_col = counter_col + 1
WHERE primary_key = value;

-- Collection operations
UPDATE table_name
SET list_col = list_col + ['new_item'],
    set_col = set_col + {'new_element'},
    map_col['key'] = 'value'
WHERE primary_key = value;

Simple Examples

-- Update single column
UPDATE users
SET email = 'alice@new.com'
WHERE user_id = ?;

-- Update multiple columns
UPDATE users
SET email = 'bob@new.com',
    phone = '+1-555-9999',
    updated_at = toTimestamp(now())
WHERE user_id = ?;

-- Update with TTL (expires after 24 hours)
UPDATE sessions
USING TTL 86400
SET session_data = '...'
WHERE session_id = ?;

Important UPDATE Rules

  • Must include ALL primary key columns in WHERE clause
  • Cannot update primary key columns themselves
  • Always creates row if missing (UPSERT behavior)
  • No RETURNING clause (can't get old values)

🔄 How UPDATE Works Internally

UPDATE Execution Flow 1️⃣ Client Sends UPDATE UPDATE users SET email=? WHERE user_id=? 2️⃣ Coordinator: Parse & Route ✓ Hash partition key → Find replica nodes ✓ No read required (blind write) 3️⃣ Write to Memtable (In-Memory) 💾 New value written to memory Timestamp: current time (conflict resolution) 4️⃣ Append to Commit Log (Durability) ✓ Write to append-only log on disk ✓ Ensures data survives crashes 5️⃣ Acknowledge Success ✅ UPDATE confirmed to client 🔄 Background (Later) • Memtable → SSTable • Compaction merges • Replicas sync • Old values deleted (Not part of UPDATE latency) 💡 Key Facts ✓ No read before write ✓ Always succeeds ✓ Very fast (~1-5ms) ✓ Creates if missing ✓ Last write wins (Timestamp determines) winner in conflicts ⏱️ Total Time: ~1-5ms (Memtable + Commit Log only, no disk read!)

Why UPDATE is So Fast

  • No read required: Doesn't check if row exists (blind write)
  • Memory-first: Writes to Memtable (RAM) first
  • Sequential log: Commit log is append-only (very fast)
  • No immediate disk seek: SSTables written later in batch

💡 UPSERT Behavior Explained

UPDATE = UPSERT in Cassandra Scenario 1: Row EXISTS BEFORE UPDATE user_id: 001 email: alice@old.com UPDATE users SET AFTER UPDATE (Modified) user_id: 001 email: alice@new.com ✅ UPDATED Scenario 2: Row DOESN'T EXIST BEFORE UPDATE (No row with user_id = 002) UPDATE users SET AFTER UPDATE (Created!) user_id: 002 email: bob@new.com ✨ CREATED 💡 UPSERT: UPDATE + INSERT Combined ✓ If row exists → Modifies existing columns ✓ If row doesn't exist → Creates new row with provided columns ⚠️ Other columns not mentioned in SET remain NULL (if creating new row)

UPSERT Gotchas

  • No error on missing row: UPDATE silently creates row
  • Partial row creation: Only SET columns populated, rest NULL
  • Can't check existence: Use IF EXISTS for conditional updates
  • Concurrent UPDATEs: Last write wins (timestamp-based)

UPSERT Examples

-- Example: This ALWAYS works (creates or updates)
UPDATE users
SET email = 'new@example.com',
    name = 'Alice'
WHERE user_id = uuid();
// If user_id doesn't exist → Creates new user
// If user_id exists → Updates email and name

-- ⚠️ Dangerous: Partial row if creating
UPDATE users
SET email = 'partial@example.com'
WHERE user_id = ?;
// If row doesn't exist: Creates with ONLY email
// Other columns (name, phone, etc.) will be NULL!

-- ✅ Better: Use INSERT for creation
INSERT INTO users (user_id, email, name, phone)
VALUES (?, 'complete@example.com', 'Bob', '+1-555-0000');

-- ✅ Or use conditional UPDATE
UPDATE users
SET email = 'conditional@example.com'
WHERE user_id = ?
IF EXISTS;
// Only updates if row exists, otherwise returns [applied]=false

📦 Updating Collections

LIST Operations

LIST Updates
-- Append to list (adds to end)
UPDATE user_posts
SET tags = tags + ['tech', 'cassandra']
WHERE post_id = ?;

-- Prepend to list (adds to beginning)
UPDATE user_posts
SET tags = ['featured'] + tags
WHERE post_id = ?;

-- Remove from list (removes ALL occurrences)
UPDATE user_posts
SET tags = tags - ['old-tag']
WHERE post_id = ?;

-- Replace entire list
UPDATE user_posts
SET tags = ['new', 'tags', 'here']
WHERE post_id = ?;

-- Update list element by index
UPDATE user_posts
SET tags[0] = 'updated-first-tag'
WHERE post_id = ?;

SET Operations

-- Add elements to set
UPDATE users
SET interests = interests + {'music', 'sports'}
WHERE user_id = ?;

-- Remove elements from set
UPDATE users
SET interests = interests - {'old-interest'}
WHERE user_id = ?;

-- Replace entire set
UPDATE users
SET interests = {'tech', 'gaming', 'reading'}
WHERE user_id = ?;

MAP Operations

-- Add/Update map entries
UPDATE users
SET preferences = preferences + {'theme': 'dark', 'lang': 'en'}
WHERE user_id = ?;

-- Update single map key
UPDATE users
SET preferences['theme'] = 'light'
WHERE user_id = ?;

-- Remove map keys
UPDATE users
SET preferences = preferences - {'old-key'}
WHERE user_id = ?;

-- Replace entire map
UPDATE users
SET preferences = {'theme': 'dark', 'notifications': 'on'}
WHERE user_id = ?;

Collection Update Warnings

  • Read-before-write for some ops: +/- operations may require read
  • Not atomic with other columns: Collection update + regular column update not atomic
  • Size limits: Collections shouldn't exceed ~100MB
  • LIST removal: Removes ALL occurrences of value

🔢 Counter Updates

What are Counters?

Counters are special columns that can only be incremented or decremented. Perfect for likes, views, votes without read-modify-write!

Counter Operations

Counter Updates
-- Table with counter
CREATE TABLE post_stats (
  post_id UUID PRIMARY KEY,
  views COUNTER,
  likes COUNTER,
  shares COUNTER
);

-- Increment counter
UPDATE post_stats
SET views = views + 1
WHERE post_id = ?;

-- Increment by more than 1
UPDATE post_stats
SET likes = likes + 10
WHERE post_id = ?;

-- Decrement counter
UPDATE post_stats
SET likes = likes - 1
WHERE post_id = ?;

-- Update multiple counters
UPDATE post_stats
SET views = views + 1,
    shares = shares + 1
WHERE post_id = ?;
How Counter Updates Work views = views + 1 views = views + 1 views = views + 1 Final Counter 3 ✅ Counter Benefits ✓ No read-modify-write cycle (just increment/decrement) ✓ Conflict-free (all increments eventually applied) ✓ High write throughput (perfect for views, likes, votes)

Counter Restrictions

  • ❌ Cannot mix: Counter columns and regular columns in same table
  • ❌ No SET to specific value: Can only increment/decrement
  • ❌ No conditional updates: IF clauses don't work with counters
  • ❌ Eventually consistent: May see stale values briefly

⏰ TTL (Time To Live) Updates

Setting TTL on Updates

TTL Examples
-- Update with 1 hour TTL
UPDATE sessions
USING TTL 3600
SET session_data = '...',
    last_activity = toTimestamp(now())
WHERE session_id = ?;
// Row auto-deletes after 3600 seconds

-- Update with 24 hour TTL
UPDATE temporary_data
USING TTL 86400
SET data = 'expires tomorrow'
WHERE id = ?;

-- Remove TTL (make permanent)
UPDATE sessions
USING TTL 0
SET session_data = 'permanent now'
WHERE session_id = ?;

-- Check remaining TTL
SELECT session_id, TTL(session_data)
FROM sessions
WHERE session_id = ?;
// Returns remaining seconds, NULL if no TTL

Common TTL Use Cases

  • Session data: 1-24 hours
  • Cache entries: Minutes to hours
  • Temporary tokens: 5-60 minutes
  • Rate limiting: 1-60 seconds
  • Recent activity: 7-30 days

✅ Conditional Updates (IF)

IF EXISTS / IF NOT EXISTS

-- Only update if row exists
UPDATE users
SET email = 'new@example.com'
WHERE user_id = ?
IF EXISTS;

// Returns: [applied]=true if exists, false if not

-- Only update if row doesn't exist (rare)
UPDATE users
SET email = 'first@example.com'
WHERE user_id = ?
IF NOT EXISTS;
// Better to use INSERT for this!

IF Column Conditions

-- Only update if column equals value
UPDATE users
SET email = 'new@example.com'
WHERE user_id = ?
IF email = 'old@example.com';

-- Multiple conditions (AND only)
UPDATE accounts
SET balance = balance - 100
WHERE account_id = ?
IF balance >= 100
  AND status = 'active';

-- Check NULL
UPDATE users
SET verified_at = toTimestamp(now())
WHERE user_id = ?
IF verified_at = NULL;
// Only set if not already verified

Performance Impact

Conditional updates require a read-before-write using lightweight transactions (Paxos). This makes them much slower:

  • Normal UPDATE: ~1-5ms
  • Conditional UPDATE: ~10-50ms (10x slower!)

Use conditionals sparingly - only when you NEED consistency guarantees!

⚡ Lightweight Transactions (LWT)

What are Lightweight Transactions?

LWT use Paxos consensus protocol to guarantee linearizability - ensures conditional updates are atomic and consistent across all replicas.

Lightweight Transaction (Paxos) Flow Phase 1: Prepare/Promise 1. Proposer sends PREPARE to all replicas 2. Replicas respond with PROMISE + current value Phase 2: Propose/Accept 3. Check IF condition with current values 4. If true: PROPOSE new value to quorum 5. Replicas ACCEPT and write value Phase 3: Commit/Learn 6. COMMIT acknowledged by quorum 7. Return [applied]=true to client ⚠️ Performance Cost Normal UPDATE: 1-5ms LWT UPDATE: 10-50ms (10x slower) Why So Slow? • 4 round trips between nodes • Read current value first • Consensus protocol overhead • Serial execution (not parallel) When to Use LWT? ✓ Account balance checks ✓ Unique constraint enforcement ✓ Compare-and-swap operations ✓ Critical consistency requirements ❌ High-throughput writes ❌ When eventual consistency OK

LWT Best Practices

-- ✅ GOOD: Check balance before debit
UPDATE accounts
SET balance = balance - 100
WHERE account_id = ?
IF balance >= 100;

-- ✅ GOOD: Prevent double-processing
UPDATE orders
SET status = 'processed'
WHERE order_id = ?
IF status = 'pending';

-- ❌ BAD: Using LWT unnecessarily
UPDATE page_views
SET views = views + 1
WHERE page_id = ?
IF EXISTS;
// Slow! Use regular UPDATE or counter instead

-- ❌ BAD: High-frequency LWT
UPDATE real_time_metrics
SET value = ?
WHERE metric_id = ?
IF timestamp > previous_timestamp;
// Will bottleneck! Use last-write-wins instead

💡 Real-World UPDATE Examples

Example 1: Social Media - Update Post

Scenario: User edits their post content and adds tags

-- Update post content and metadata
UPDATE user_posts
SET content = 'Updated post content here...',
    tags = tags + ['update', 'edited'],
    edited_at = toTimestamp(now()),
    edit_count = edit_count + 1
WHERE user_id = ?
  AND post_id = ?;

-- Increment like count (counter table)
UPDATE post_stats
SET likes = likes + 1
WHERE post_id = ?;

Example 2: E-commerce - Update Order Status

Scenario: Order moves through workflow stages with conditional checks

-- Update order status (with condition to prevent double-processing)
UPDATE orders
SET status = 'shipped',
    shipped_at = toTimestamp(now()),
    tracking_number = 'TRK123456',
    status_history = status_history + ['shipped:2025-01-15T10:30:00']
WHERE order_id = ?
IF status = 'processing';
// Only ship if currently processing (prevents double-ship)

-- Update inventory (with balance check)
UPDATE inventory
SET quantity = quantity - 5
WHERE product_id = ?
IF quantity >= 5;

Example 3: User Profile - Preferences Update

Scenario: User updates profile settings with map operations

-- Update user preferences (map operations)
UPDATE user_profiles
SET preferences['theme'] = 'dark',
    preferences['language'] = 'en',
    preferences['notifications'] = 'email',
    last_updated = toTimestamp(now())
WHERE user_id = ?;

-- Add interests (set operation)
UPDATE user_profiles
SET interests = interests + {'technology', 'music', 'sports'}
WHERE user_id = ?;

-- Remove old interests
UPDATE user_profiles
SET interests = interests - {'outdated-interest'}
WHERE user_id = ?;

Example 4: Session Management

Scenario: Update session with TTL for auto-expiration

-- Extend session with 1 hour TTL
UPDATE user_sessions
USING TTL 3600
SET session_data = '{"page": "dashboard", "cart_items": 3}',
    last_activity = toTimestamp(now()),
    page_views = page_views + 1
WHERE session_id = ?;

-- Update shopping cart (expires with session)
UPDATE shopping_carts
USING TTL 3600
SET items = items + {
    'product_123': '{"qty": 2, "price": 29.99}'
  }
WHERE session_id = ?;

Example 5: Gaming - Player Stats

Scenario: Update player statistics after game completion

-- Update player stats (counters)
UPDATE player_stats
SET games_played = games_played + 1,
    total_score = total_score + 1500,
    wins = wins + 1
WHERE player_id = ?;

-- Update leaderboard score
UPDATE leaderboard
SET best_score = 9500,
    achievements = achievements + ['high_scorer', 'speed_demon'],
    last_played = toTimestamp(now())
WHERE player_id = ?
  AND season = 2025
IF best_score < 9500;
// Only update if new score is better

Example 6: Financial - Account Transaction

Scenario: Debit account with balance check (LWT)

-- Withdraw money (with balance check)
UPDATE accounts
SET balance = balance - 250.00,
    last_transaction = toTimestamp(now()),
    transaction_count = transaction_count + 1
WHERE account_id = ?
IF balance >= 250.00
  AND status = 'active';

// Check result
// [applied]=true → Success
// [applied]=false → Insufficient funds or inactive

-- Record transaction history
UPDATE transaction_history
SET transactions = transactions + [
    '{"type": "debit", "amount": 250.00, "timestamp": "2025-01-15T14:30:00"}'
  ]
WHERE account_id = ?
  AND year = 2025
  AND month = 1;

✅ UPDATE Best Practices

✅ DO These Things

  • ✓ Use prepared statements
  • ✓ Include ALL primary key columns
  • ✓ Use counters for metrics
  • ✓ Set TTL for temporary data
  • ✓ Use IF sparingly (performance)
  • ✓ Batch updates to same partition
  • ✓ Monitor LWT performance
  • ✓ Handle [applied]=false responses

❌ DON'T Do These

  • ✗ Update primary key columns
  • ✗ Overuse LWT (slow!)
  • ✗ Mix counters with regular columns
  • ✗ Forget to check [applied] result
  • ✗ Batch across partitions
  • ✗ Use IF for high-throughput writes
  • ✗ Assume UPDATE checks existence
  • ✗ Set very large collections

Golden Rules for UPDATE

  1. Understand UPSERT: UPDATE creates if missing - use INSERT when you want all columns populated
  2. Use counters properly: Dedicated counter tables, can't mix with regular columns
  3. LWT is expensive: 10x slower than regular updates - use only when consistency critical
  4. TTL for expiration: Let Cassandra auto-delete instead of manual cleanup
  5. Collection operations: Append/prepend to lists, add/remove from sets, update map keys
  6. Handle conditional failures: Check [applied] result, retry with backoff if needed

Performance Comparison

Operation Type Latency When to Use
Regular UPDATE ⚡ 1-5ms Most updates, eventual consistency OK
Counter UPDATE ⚡ 1-5ms Metrics, likes, views (no read needed)
TTL UPDATE ⚡ 1-5ms Sessions, cache, temporary data
Collection UPDATE ⚠️ 2-10ms May require read for +/- operations
IF EXISTS 🐌 10-50ms Prevent creating unwanted rows
IF condition (LWT) 🐌 10-50ms Balance checks, uniqueness, CAS

Common Pitfalls & Solutions

Pitfall 1: Assuming UPDATE checks existence

Problem: UPDATE creates row if missing (UPSERT)

Solution: Use IF EXISTS or INSERT for explicit creation

Pitfall 2: Overusing LWT

Problem: Conditional updates are 10x slower

Solution: Only use when consistency is CRITICAL

Pitfall 3: Large collection updates

Problem: Collections > 100MB cause performance issues

Solution: Split into multiple rows or use denormalization

Pitfall 4: Trying to update primary key

Problem: Cannot update partition/clustering keys

Solution: INSERT new row, DELETE old row

Previous
Select
Advertisement

Responsive Ad