Combining INSERT and UPDATE on Same Table in SQLite

Understanding SQLite’s Single-Statement Insert-Update Challenges

Core Challenge: Atomic Modification of Table State

The fundamental challenge revolves around performing both row insertion and data modification within a single atomic operation on the same SQLite table. This requires navigating three critical constraints:

1. Transaction Isolation Rules: SQLite uses strict write-ahead logging (WAL) that serializes table modifications
2. Statement-Level Atomicity: Each INSERT/UPDATE/DELETE statement operates as an implicit transaction
3. Table Lock Hierarchy: SQLite employs table-level locks during write operations

When attempting concurrent insert-update patterns, developers encounter these technical realities:

• A writing statement (INSERT/UPDATE) obtains RESERVED lock on target table
• No subsequent write operations allowed until lock release
• Read operations (SELECT) from same connection use snapshot isolation
• Foreign key/trigger cascades operate under special exception rules

This creates an environment where attempting to modify existing rows while inserting new ones requires explicit control flow mechanisms beyond basic SQL syntax.

Architectural Constraints Enabling Conflict

Four key factors create the impossibility of native insert-update fusion:

1. CTE Read-Only Limitation
Common Table Expressions serve as temporary named result sets. While they can reference multiple tables and perform complex joins, they fundamentally operate as read-only data sources when used in DML statements. CTEs cannot directly execute UPDATE/DELETE operations within their definition.

2. Single-Table Modification Per Statement
SQLite enforces that any INSERT, UPDATE, or DELETE statement may only affect rows in one base table. This includes indirect modifications through triggers. Attempting to modify multiple tables in a single statement via CTEs or subqueries violates parser constraints.

3. Trigger Execution Ordering
Before/after triggers fire in defined sequence but cannot create interleaved insert-update logic:

CREATE TRIGGER msg_after_insert 
AFTER INSERT ON Message
BEGIN
    UPDATE Message SET hash = compute_hash(NEW.timestamp) 
    WHERE timestamp = NEW.timestamp; -- Blocked by table lock!
END;

This trigger would deadlock because the INSERT statement still holds exclusive lock on Message table during trigger execution.

4. UPSERT Clause Limitations
The ON CONFLICT clause supports updating only the conflicting row:

INSERT INTO Message(timestamp, hash)
VALUES (1712345678, 'abc123')
ON CONFLICT(timestamp) 
DO UPDATE SET hash = excluded.hash;

This updates existing rows on primary key conflict but cannot modify unrelated rows.

Comprehensive Resolution Strategies

Solution 1: Transactional Batch Processing with Temp Tables

Step 1: Create staging area

CREATE TEMP TABLE Staging(
    timestamp INTEGER PRIMARY KEY,
    hash TEXT,
    action TEXT CHECK(action IN ('INSERT','UPDATE'))
);

Step 2: Populate staging with logic

WITH Changes AS (
    SELECT 1712345678 AS ts, 'abc123' AS h
    UNION ALL
    SELECT 1712345680, 'def456'
)
INSERT INTO Staging
SELECT ts, h,
    CASE WHEN EXISTS(
        SELECT 1 FROM Message WHERE timestamp = ts
    ) THEN 'UPDATE' ELSE 'INSERT' END
FROM Changes;

Step 3: Execute batched modifications

BEGIN IMMEDIATE;

-- Process inserts first
INSERT INTO Message(timestamp, hash)
SELECT timestamp, hash 
FROM Staging 
WHERE action = 'INSERT'
ON CONFLICT DO NOTHING;

-- Follow with updates
UPDATE Message 
SET hash = (
    SELECT hash FROM Staging 
    WHERE Staging.timestamp = Message.timestamp 
    AND action = 'UPDATE'
)
WHERE EXISTS (
    SELECT 1 FROM Staging 
    WHERE Staging.timestamp = Message.timestamp 
    AND action = 'UPDATE'
);

COMMIT;

Solution 2: Recursive Trigger Chains with Shadow Columns

Phase 1: Add control column

ALTER TABLE Message ADD COLUMN pending_hash TEXT;

Phase 2: Create insert trigger

CREATE TRIGGER msg_insert_processor
AFTER INSERT ON Message
FOR EACH ROW
WHEN NEW.pending_hash IS NOT NULL
BEGIN
    UPDATE Message 
    SET hash = NEW.pending_hash,
        pending_hash = NULL
    WHERE timestamp = NEW.timestamp;
END;

Phase 3: Create update trigger

CREATE TRIGGER msg_update_propagator
AFTER UPDATE OF pending_hash ON Message
FOR EACH ROW
WHEN NEW.pending_hash IS NOT NULL
BEGIN
    UPDATE Message 
    SET hash = NEW.pending_hash,
        pending_hash = NULL
    WHERE timestamp = NEW.timestamp;
END;

Execution pattern:

-- Insert with deferred hash
INSERT INTO Message(timestamp, pending_hash)
VALUES (1712345678, 'abc123');

-- Update via shadow column
UPDATE Message 
SET pending_hash = 'def456' 
WHERE timestamp = 1712345680;

Solution 3: Window Function-Based Conditional Logic

For complex insert-update patterns using analytical processing:

WITH RankedData AS (
    SELECT 
        timestamp,
        hash,
        ROW_NUMBER() OVER(
            PARTITION BY timestamp 
            ORDER BY source_priority
        ) AS rn
    FROM (
        -- Existing table data as 'UPDATE' source
        SELECT timestamp, hash, 2 AS source_priority
        FROM Message
        WHERE timestamp BETWEEN 1712345000 AND 1712346000
        
        UNION ALL
        
        -- New data as 'INSERT' source  
        SELECT 1712345678 AS timestamp, 'abc123' AS hash, 1 AS source_priority
    )
)
INSERT INTO Message(timestamp, hash)
SELECT timestamp, hash
FROM RankedData
WHERE rn = 1
ON CONFLICT(timestamp) DO UPDATE
SET hash = excluded.hash;

Solution 4: SQLite C Extension for Custom VFS

For extreme performance requirements, implement a virtual table driver that handles insert-update fusion at the storage layer:

1. Create union_vfs extending default VFS
2. Intercept write operations:

static int xWrite(
    sqlite3_file *pFile,
    const void *zBuf,
    int iAmt,
    sqlite3_int64 iOfst
){
    // Detect insert vs update patterns
    if(is_insert_operation(zBuf)){
        modify_page_for_upsert(zBuf, iOfst);
    }
    return SQLITE_OK;
}

3. Register custom VFS at runtime:

sqlite3_vfs_register(&union_vfs, 1);

Solution 5: Connection Pooling with Write Batching

Using multiple database connections to bypass lock contention:

1. Connection 1 (Read/Insert):

ATTACH DATABASE ':memory:' AS mem;
BEGIN IMMEDIATE;
INSERT INTO main.Message ...;
-- Detected changes copied to memory DB
COMMIT;

2. Connection 2 (Update):

ATTACH DATABASE ':memory:' AS mem;
BEGIN EXCLUSIVE;
UPDATE main.Message 
SET hash = mem.Message.hash 
WHERE timestamp IN (SELECT timestamp FROM mem.Message);
COMMIT;

Final Recommendation: For most use cases, Solution 1 (Transactional Batch Processing) provides optimal balance between complexity and performance. Implement Solution 4 (C Extension) only when handling >100K operations/second. Use Solution 2 (Trigger Chains) for legacy system integration where schema changes are prohibited.