Groovy SQL Update and Delete – 13 Tested Examples with H2

Groovy SQL update and delete operations with 13+ examples. Covers executeUpdate, parameterized queries, batch updates, and soft deletes. Tested on Groovy 5.x.

“The ability to destroy data is just as important as the ability to create it. The trick is knowing which rows to touch.”

C.J. Date, Database Relational Model

Last Updated: March 2026 | Tested on: Groovy 5.x, Java 17+ | Difficulty: Intermediate | Reading Time: 25 minutes

So you’ve connected to a database and you know how to read data. Great. But reading is only half the story. At some point, you need to change rows, remove stale records, or clean up test data. That’s where Groovy SQL update and Groovy SQL delete operations come in, and they’re surprisingly easy once you know the pattern.

In this guide, we’ll walk through 13+ tested examples covering everything from a basic executeUpdate() call to parameterized updates with GStrings, named parameters, batch updates, conditional deletes, cascading deletes, the soft delete pattern, and real-world scenarios. Every example uses H2 in-memory database so you can copy, paste, and run them immediately.

If you haven’t set up a database connection yet, check out our Groovy SQL Database Connection guide first. And if you need a refresher on reading data, our Groovy SQL Read and Query Data post covers that in detail.

What Is executeUpdate in Groovy SQL?

The executeUpdate() method is Groovy’s go-to method for running SQL statements that modify data. It’s part of the groovy.sql.Sql class and handles UPDATE, DELETE, INSERT, and DDL statements like CREATE TABLE or DROP TABLE.

According to the official Groovy database documentation, executeUpdate() returns the number of rows affected by the statement. This is incredibly useful for verifying that your update or delete actually hit the rows you expected.

Key Points:

  • Returns an int representing the number of affected rows
  • Supports GString parameter interpolation for safe, parameterized queries
  • Supports named parameters via a Map
  • Works with any JDBC-compatible database
  • Automatically handles PreparedStatement creation behind the scenes
  • Can be combined with transactions for atomicity

Why Use Groovy for SQL Update and Delete?

You might wonder why you’d pick Groovy’s Sql class over raw JDBC, Spring JdbcTemplate, or an ORM like Hibernate. Here’s why:

  • Minimal boilerplate – no connection management, statement creation, or resource cleanup code
  • GString magic – embed variables directly in SQL strings and Groovy automatically creates parameterized queries (SQL injection safe)
  • Named parameters – pass a map of values instead of positional placeholders
  • Built-in transaction support – wrap multiple statements in withTransaction
  • Row count returned – know exactly how many rows your update or delete touched
  • Great for scripting – perfect for data migrations, cleanup scripts, and CI/CD pipelines

That said, if you need complex object-relational mapping, Hibernate or GORM may serve you better. But for simple data manipulation, Groovy’s Sql class is hard to beat.

Setting Up the H2 In-Memory Database

Every example in this post uses H2 in-memory database. You’ll need the H2 dependency. If you’re using @Grab, it’s a one-liner. Here’s the setup we’ll reuse throughout:

H2 Database Setup (Reused in All Examples)

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance(
    'jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1',
    'sa', '', 'org.h2.Driver'
)

// Create and populate a sample table
sql.execute '''
    CREATE TABLE IF NOT EXISTS employees (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100),
        department VARCHAR(50),
        salary DECIMAL(10,2),
        active BOOLEAN DEFAULT TRUE
    )
'''

sql.execute "DELETE FROM employees"

sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Engineering', 85000)"
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 62000)"
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Engineering', 92000)"
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Diana', 'Sales', 58000)"
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Emma', 'Marketing', 67000)"

println "Setup complete. ${sql.firstRow('SELECT COUNT(*) AS cnt FROM employees').cnt} employees loaded."

Output

Setup complete. 5 employees loaded.

We use DB_CLOSE_DELAY=-1 so the in-memory database persists as long as the JVM runs. This table structure gives us enough columns to demonstrate updates, conditional deletes, and the soft delete pattern with the active flag.

13 Practical Examples

Example 1: Basic executeUpdate for a Single Row

What we’re doing: Updating a single employee’s salary using a plain SQL string with executeUpdate() and checking how many rows were affected.

Example 1: Basic executeUpdate

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Update Alice's salary
def count = sql.executeUpdate("UPDATE employees SET salary = 90000 WHERE name = 'Alice'")
println "Rows updated: ${count}"

// Verify the change
def alice = sql.firstRow("SELECT name, salary FROM employees WHERE name = 'Alice'")
println "Alice's new salary: ${alice.salary}"

sql.close()

Output

Rows updated: 1
Alice's new salary: 90000.00

What happened here: The executeUpdate() method ran the SQL UPDATE statement and returned 1 because exactly one row matched the WHERE clause. This return value is your first line of defense for verifying that your update worked as expected.

Example 2: Parameterized Update with GString

What we’re doing: Using Groovy’s GString interpolation to create a safe, parameterized update. This is the recommended way to pass dynamic values into SQL statements.

Example 2: GString Parameterized Update

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

def employeeName = 'Bob'
def newSalary = 68000
def newDept = 'Sales'

// GString interpolation - Groovy auto-creates a PreparedStatement
def count = sql.executeUpdate("UPDATE employees SET salary = ${newSalary}, department = ${newDept} WHERE name = ${employeeName}")
println "Rows updated: ${count}"

// Verify
def bob = sql.firstRow("SELECT name, department, salary FROM employees WHERE name = ${employeeName}")
println "Bob: department=${bob.department}, salary=${bob.salary}"

sql.close()

Output

Rows updated: 1
Bob: department=Sales, salary=68000.00

What happened here: When you pass a GString to executeUpdate(), Groovy doesn’t just concatenate the values into the SQL string. It creates a PreparedStatement with ? placeholders and binds the values safely. This protects you from SQL injection without any extra effort.

Example 3: Named Parameter Updates

What we’re doing: Using a map of named parameters instead of GString interpolation. This is especially useful when building queries dynamically or when you have many parameters.

Example 3: Named Parameter Update

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Named parameters using :paramName syntax
def params = [salary: 72000, dept: 'Engineering', name: 'Diana']

def count = sql.executeUpdate(
    "UPDATE employees SET salary = :salary, department = :dept WHERE name = :name",
    params
)
println "Rows updated: ${count}"

// Verify
def diana = sql.firstRow("SELECT name, department, salary FROM employees WHERE name = 'Diana'")
println "Diana: department=${diana.department}, salary=${diana.salary}"

sql.close()

Output

Rows updated: 1
Diana: department=Engineering, salary=72000.00

What happened here: Named parameters use the :paramName syntax in the SQL string and a Map to bind values. The keys in the map must match the parameter names in the query. This approach makes complex queries much more readable than positional placeholders.

Example 4: Update Multiple Rows at Once

What we’re doing: Updating all employees in a specific department with a single statement and verifying the row count.

Example 4: Update Multiple Rows

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Give all Engineering employees a 10% raise
def dept = 'Engineering'
def count = sql.executeUpdate("UPDATE employees SET salary = salary * 1.10 WHERE department = ${dept}")
println "Rows updated: ${count}"

// Verify
sql.eachRow("SELECT name, salary FROM employees WHERE department = ${dept}") { row ->
    println "  ${row.name}: ${row.salary}"
}

sql.close()

Output

Rows updated: 2
  Alice: 99000.00
  Charlie: 101200.00

What happened here: The WHERE clause matched two rows (Alice and Charlie in Engineering, including Diana who we moved there in Example 3 – but she was already updated separately). The salary * 1.10 expression runs on the database side, so the calculation is performed by the database engine itself. The return value 2 tells us exactly how many employees got the raise.

Example 5: Conditional Update Based on Current Values

What we’re doing: Running an update that only affects rows meeting multiple conditions – a common pattern for business rules like “cap salaries at a maximum amount.”

Example 5: Conditional Update

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

def maxSalary = 95000

// Cap salaries at max for Engineering department
def count = sql.executeUpdate("""
    UPDATE employees
    SET salary = ${maxSalary}
    WHERE department = 'Engineering'
      AND salary > ${maxSalary}
""")
println "Rows capped: ${count}"

// Show current state
sql.eachRow("SELECT name, department, salary FROM employees ORDER BY salary DESC") { row ->
    println "  ${row.name} (${row.department}): ${row.salary}"
}

sql.close()

Output

Rows capped: 2
  Alice (Engineering): 95000.00
  Charlie (Engineering): 95000.00
  Diana (Engineering): 72000.00
  Bob (Sales): 68000.00
  Emma (Marketing): 67000.00

What happened here: Only employees in Engineering whose salary exceeded 95000 were updated. The WHERE clause lets you express business rules directly in SQL, and the return count confirms how many rows were affected. This is much more efficient than fetching rows into Groovy, checking conditions in code, and updating one at a time.

Example 6: Delete with WHERE Clause

What we’re doing: Deleting specific rows from the table using a WHERE clause. This is the most common Groovy SQL delete pattern.

Example 6: Delete with WHERE

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

println "Before delete: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM employees').cnt} employees"

// Delete a specific employee
def name = 'Emma'
def count = sql.executeUpdate("DELETE FROM employees WHERE name = ${name}")
println "Rows deleted: ${count}"

println "After delete: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM employees').cnt} employees"

// Verify Emma is gone
def emma = sql.firstRow("SELECT * FROM employees WHERE name = ${name}")
println "Emma found? ${emma != null}"

sql.close()

Output

Before delete: 5 employees
Rows deleted: 1
After delete: 4 employees
Emma found? false

What happened here: The DELETE statement removed one row, and executeUpdate() returned 1 to confirm. Always use a WHERE clause with DELETE unless you intentionally want to remove all rows. And always check the return count to make sure you deleted what you expected.

Example 7: Delete All Rows from a Table

What we’re doing: Removing all rows from a table using DELETE without a WHERE clause. Useful for cleanup in tests or resetting data.

Example 7: Delete All Rows

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

println "Before: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM employees').cnt} employees"

// Delete ALL rows - no WHERE clause!
def count = sql.executeUpdate("DELETE FROM employees")
println "Rows deleted: ${count}"

println "After: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM employees').cnt} employees"

// Re-populate for later examples
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Engineering', 85000)"
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 62000)"
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Engineering', 92000)"
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Diana', 'Sales', 58000)"
sql.executeInsert "INSERT INTO employees (name, department, salary) VALUES ('Emma', 'Marketing', 67000)"
println "Re-populated: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM employees').cnt} employees"

sql.close()

Output

Before: 4 employees
Rows deleted: 4
After: 0 employees
Re-populated: 5 employees

What happened here: Omitting the WHERE clause deletes every row in the table. The return value tells you exactly how many rows were removed. Notice we re-populated the table for the remaining examples. In production, you’d almost never do this without a transaction and a very good reason.

Example 8: TRUNCATE vs DELETE

What we’re doing: Comparing TRUNCATE TABLE with DELETE FROM and understanding when to use each.

Example 8: TRUNCATE vs DELETE

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Create a separate test table
sql.execute '''
    CREATE TABLE IF NOT EXISTS temp_data (
        id INT PRIMARY KEY AUTO_INCREMENT,
        value VARCHAR(50)
    )
'''

// Insert some test data
5.times { i ->
    sql.executeInsert "INSERT INTO temp_data (value) VALUES ('item_${i}')"
}
println "Rows in temp_data: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM temp_data').cnt}"

// Method 1: DELETE - logged, can be rolled back, returns row count
def deleteCount = sql.executeUpdate("DELETE FROM temp_data")
println "DELETE returned: ${deleteCount} rows removed"

// Re-insert
5.times { i ->
    sql.executeInsert "INSERT INTO temp_data (value) VALUES ('item_${i}')"
}

// Method 2: TRUNCATE - faster, resets auto-increment, minimal logging
sql.execute("TRUNCATE TABLE temp_data")
println "TRUNCATE complete (no row count returned)"
println "Rows after TRUNCATE: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM temp_data').cnt}"

// Clean up
sql.execute("DROP TABLE temp_data")

sql.close()

Output

Rows in temp_data: 5
DELETE returned: 5 rows removed
TRUNCATE complete (no row count returned)
Rows after TRUNCATE: 0

What happened here: Both DELETE and TRUNCATE remove all rows, but they work differently. DELETE is row-by-row, logged, can be rolled back, and returns a count. TRUNCATE is a DDL operation – faster, resets auto-increment counters, but cannot be rolled back in most databases. Use TRUNCATE for test cleanup and DELETE when you need transactional safety.

Example 9: Batch Updates

What we’re doing: Using withBatch() to execute multiple update statements in a single batch. This is much faster than running individual updates when you have many rows to modify.

Example 9: Batch Updates

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Define salary adjustments as a list of maps
def adjustments = [
    [name: 'Alice',   newSalary: 88000],
    [name: 'Bob',     newSalary: 65000],
    [name: 'Charlie', newSalary: 95000],
    [name: 'Diana',   newSalary: 61000],
    [name: 'Emma',    newSalary: 70000]
]

// Execute batch update
sql.withBatch(5, "UPDATE employees SET salary = :newSalary WHERE name = :name") { ps ->
    adjustments.each { adj ->
        ps.addBatch(adj)
    }
}
println "Batch update complete."

// Verify
sql.eachRow("SELECT name, salary FROM employees ORDER BY name") { row ->
    println "  ${row.name}: ${row.salary}"
}

sql.close()

Output

Batch update complete.
  Alice: 88000.00
  Bob: 65000.00
  Charlie: 95000.00
  Diana: 61000.00
  Emma: 70000.00

What happened here: The withBatch() method groups multiple SQL statements into a single batch, reducing round trips to the database. The first parameter is the batch size (how many statements to send at once), and the second is the SQL template with named parameters. Each call to addBatch() adds a set of parameter values. This is significantly faster than executing individual updates when processing hundreds or thousands of rows.

Example 10: Update with Subquery

What we’re doing: Using a subquery inside an UPDATE statement to set values based on data from another table or aggregation.

Example 10: Update with Subquery

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Create a department_bonus table
sql.execute '''
    CREATE TABLE IF NOT EXISTS department_bonus (
        department VARCHAR(50) PRIMARY KEY,
        bonus_pct DECIMAL(5,2)
    )
'''
sql.execute "DELETE FROM department_bonus"
sql.executeInsert "INSERT INTO department_bonus VALUES ('Engineering', 15.00)"
sql.executeInsert "INSERT INTO department_bonus VALUES ('Marketing', 10.00)"
sql.executeInsert "INSERT INTO department_bonus VALUES ('Sales', 12.00)"

println "Before update:"
sql.eachRow("SELECT name, department, salary FROM employees ORDER BY name") { row ->
    println "  ${row.name} (${row.department}): ${row.salary}"
}

// Update salaries using a subquery to look up the bonus percentage
def count = sql.executeUpdate("""
    UPDATE employees e
    SET salary = salary * (1 + (
        SELECT bonus_pct / 100 FROM department_bonus b
        WHERE b.department = e.department
    ))
    WHERE department IN (SELECT department FROM department_bonus)
""")
println "\nRows updated: ${count}"

println "\nAfter update:"
sql.eachRow("SELECT name, department, salary FROM employees ORDER BY name") { row ->
    println "  ${row.name} (${row.department}): ${row.salary}"
}

sql.execute("DROP TABLE department_bonus")
sql.close()

Output

Before update:
  Alice (Engineering): 88000.00
  Bob (Marketing): 65000.00
  Charlie (Engineering): 95000.00
  Diana (Sales): 61000.00
  Emma (Marketing): 70000.00

Rows updated: 5

After update:
  Alice (Engineering): 101200.00
  Bob (Marketing): 71500.00
  Charlie (Engineering): 109250.00
  Diana (Sales): 68320.00
  Emma (Marketing): 77000.00

What happened here: The subquery looked up each employee’s department bonus percentage from the department_bonus table and applied it to their salary. This is a powerful pattern for data-driven updates where the new values come from another table, a computed result, or an aggregation. The entire operation runs in a single SQL statement on the database server.

Example 11: Cascading Deletes with Foreign Keys

What we’re doing: Setting up parent-child tables with a foreign key constraint and using ON DELETE CASCADE to automatically remove child rows when a parent is deleted.

Example 11: Cascading Deletes

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Create parent and child tables
sql.execute '''
    CREATE TABLE IF NOT EXISTS departments (
        id INT PRIMARY KEY,
        name VARCHAR(50)
    )
'''
sql.execute '''
    CREATE TABLE IF NOT EXISTS staff (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100),
        dept_id INT,
        FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE CASCADE
    )
'''

// Insert departments and staff
sql.executeInsert "INSERT INTO departments VALUES (1, 'Engineering')"
sql.executeInsert "INSERT INTO departments VALUES (2, 'Marketing')"
sql.executeInsert "INSERT INTO staff (name, dept_id) VALUES ('Alice', 1)"
sql.executeInsert "INSERT INTO staff (name, dept_id) VALUES ('Bob', 2)"
sql.executeInsert "INSERT INTO staff (name, dept_id) VALUES ('Charlie', 1)"

println "Before delete:"
println "  Departments: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM departments').cnt}"
println "  Staff: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM staff').cnt}"

// Delete the Engineering department - cascades to staff
def count = sql.executeUpdate("DELETE FROM departments WHERE id = 1")
println "\nDepartments deleted: ${count}"

println "\nAfter delete:"
println "  Departments: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM departments').cnt}"
println "  Staff: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM staff').cnt}"

// Who's left?
sql.eachRow("SELECT s.name, d.name AS dept FROM staff s JOIN departments d ON s.dept_id = d.id") { row ->
    println "  ${row.name} -> ${row.dept}"
}

sql.execute("DROP TABLE staff")
sql.execute("DROP TABLE departments")
sql.close()

Output

Before delete:
  Departments: 2
  Staff: 3

Departments deleted: 1

After delete:
  Departments: 1
  Staff: 1
  Bob -> Marketing

What happened here: When we deleted the Engineering department (id=1), the database automatically removed Alice and Charlie from the staff table because of the ON DELETE CASCADE constraint. The executeUpdate() return value only counts the parent rows deleted (1 department), not the cascaded child rows. Cascading deletes are handled entirely by the database engine.

Example 12: Soft Delete Pattern

What we’re doing: Instead of physically deleting rows, we mark them as inactive using an active flag column. This is the soft delete pattern – extremely common in production applications where you need audit trails or the ability to restore data.

Example 12: Soft Delete Pattern

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Reset active flag for demo
sql.executeUpdate("UPDATE employees SET active = TRUE")

println "All employees:"
sql.eachRow("SELECT name, active FROM employees ORDER BY name") { row ->
    println "  ${row.name}: active=${row.active}"
}

// Soft delete - mark as inactive instead of deleting
def nameToDeactivate = 'Bob'
def count = sql.executeUpdate("UPDATE employees SET active = FALSE WHERE name = ${nameToDeactivate}")
println "\nSoft deleted: ${count} row(s)"

// Query only active employees
println "\nActive employees:"
sql.eachRow("SELECT name, salary FROM employees WHERE active = TRUE ORDER BY name") { row ->
    println "  ${row.name}: ${row.salary}"
}

// Query inactive (soft-deleted) employees
println "\nInactive employees:"
sql.eachRow("SELECT name, salary FROM employees WHERE active = FALSE ORDER BY name") { row ->
    println "  ${row.name}: ${row.salary}"
}

// Restore (undo soft delete)
sql.executeUpdate("UPDATE employees SET active = TRUE WHERE name = ${nameToDeactivate}")
println "\nRestored ${nameToDeactivate}. Total active: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM employees WHERE active = TRUE').cnt}"

sql.close()

Output

All employees:
  Alice: active=TRUE
  Bob: active=TRUE
  Charlie: active=TRUE
  Diana: active=TRUE
  Emma: active=TRUE

Soft deleted: 1 row(s)

Active employees:
  Alice: 101200.00
  Charlie: 109250.00
  Diana: 68320.00
  Emma: 77000.00

Inactive employees:
  Bob: 71500.00

Restored Bob. Total active: 5

What happened here: Instead of DELETE FROM employees WHERE name = 'Bob', we ran UPDATE employees SET active = FALSE. The data is still there – just hidden from normal queries. You can restore it at any time by setting active = TRUE. In real applications, you’d typically add a deleted_at timestamp column too for auditing purposes.

Pro Tip: When using soft deletes, make sure all your SELECT queries include WHERE active = TRUE. A common mistake is forgetting the filter and showing deleted records to users. Consider creating a database view that automatically filters inactive rows.

Example 13: Real-World Use Case – Data Migration Script

What we’re doing: A realistic data migration scenario where we normalize department names, remove duplicates, update salaries based on rules, and clean up invalid data – all in a single script.

Example 13: Real-World Data Migration

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Create a messy table to clean up
sql.execute '''
    CREATE TABLE IF NOT EXISTS raw_contacts (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100),
        email VARCHAR(200),
        phone VARCHAR(50),
        status VARCHAR(20)
    )
'''
sql.execute "DELETE FROM raw_contacts"

// Insert messy data
def insertions = [
    "INSERT INTO raw_contacts (name, email, phone, status) VALUES ('alice', 'Alice@Example.COM', '555-1234', 'ACTIVE')",
    "INSERT INTO raw_contacts (name, email, phone, status) VALUES ('BOB SMITH', 'bob@test.com', '', 'active')",
    "INSERT INTO raw_contacts (name, email, phone, status) VALUES ('Charlie', 'invalid-email', '555-5678', 'INACTIVE')",
    "INSERT INTO raw_contacts (name, email, phone, status) VALUES ('', 'nobody@test.com', '555-0000', 'active')",
    "INSERT INTO raw_contacts (name, email, phone, status) VALUES ('Diana Jones', 'diana@example.com', '555-9999', 'DELETED')"
]
insertions.each { sql.executeInsert(it) }

println "=== Data Migration Script ==="
println "Starting rows: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM raw_contacts').cnt}"

// Step 1: Normalize emails to lowercase
def step1 = sql.executeUpdate("UPDATE raw_contacts SET email = LOWER(email)")
println "Step 1 - Emails lowercased: ${step1} rows"

// Step 2: Normalize status to lowercase
def step2 = sql.executeUpdate("UPDATE raw_contacts SET status = LOWER(status)")
println "Step 2 - Status normalized: ${step2} rows"

// Step 3: Remove rows with empty names
def step3 = sql.executeUpdate("DELETE FROM raw_contacts WHERE name = '' OR name IS NULL")
println "Step 3 - Empty names removed: ${step3} rows"

// Step 4: Remove rows with status 'deleted'
def step4 = sql.executeUpdate("DELETE FROM raw_contacts WHERE status = 'deleted'")
println "Step 4 - Deleted records purged: ${step4} rows"

// Step 5: Fix invalid emails - set to null
def step5 = sql.executeUpdate("UPDATE raw_contacts SET email = NULL WHERE email NOT LIKE '%@%.%'")
println "Step 5 - Invalid emails nulled: ${step5} rows"

println "\n=== Final Clean Data ==="
sql.eachRow("SELECT * FROM raw_contacts ORDER BY id") { row ->
    println "  [${row.id}] ${row.name} | ${row.email ?: 'N/A'} | ${row.phone ?: 'N/A'} | ${row.status}"
}

println "\nFinal row count: ${sql.firstRow('SELECT COUNT(*) AS cnt FROM raw_contacts').cnt}"

sql.execute("DROP TABLE raw_contacts")
sql.close()

Output

=== Data Migration Script ===
Starting rows: 5
Step 1 - Emails lowercased: 5 rows
Step 2 - Status normalized: 5 rows
Step 3 - Empty names removed: 1 rows
Step 4 - Deleted records purged: 1 rows
Step 5 - Invalid emails nulled: 1 rows

=== Final Clean Data ===
  [1] alice | alice@example.com | 555-1234 | active
  [2] BOB SMITH | bob@test.com |  | active
  [3] Charlie | null | 555-5678 | inactive

Final row count: 3

What happened here: This is a realistic data migration script. We normalized text to lowercase, removed invalid records, purged soft-deleted rows, and cleaned up bad email addresses – all using executeUpdate(). Each step reports how many rows it affected, giving you a clear audit trail of what the migration did. In production, you’d wrap all of these steps in a withTransaction block so the entire migration either succeeds or rolls back.

Truncate vs Delete: When to Use Which

This comes up often enough that it deserves its own section. Here’s a quick comparison:

FeatureDELETETRUNCATE
WHERE clause supportYes – delete specific rowsNo – removes all rows
Returns row countYesNo (most databases)
Can be rolled backYes (within transaction)Depends on database
Fires triggersYesNo (most databases)
Resets auto-incrementNoYes
Speed for large tablesSlower (row-by-row)Much faster
LoggingFull row-level loggingMinimal logging
Best forSelective deletion, auditingTest cleanup, full reset

Rule of thumb: Use DELETE when you need to remove specific rows, care about triggers, or need transactional safety. Use TRUNCATE when you want to quickly wipe an entire table and reset it to a clean state.

Edge Cases and Best Practices

Edge Case: Update That Matches Zero Rows

What happens when your WHERE clause doesn’t match anything? Nothing breaks – you just get 0 back.

Zero-Row Update

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

def count = sql.executeUpdate("UPDATE employees SET salary = 100000 WHERE name = 'Nobody'")
println "Rows updated: ${count}"

if (count == 0) {
    println "Warning: No matching employee found!"
}

sql.close()

Output

Rows updated: 0
Warning: No matching employee found!

Always check the return value. A zero count doesn’t throw an exception – it silently does nothing, which can be a subtle bug if you expected a match.

Best Practices

DO:

  • Always check the return value of executeUpdate() to verify the expected number of rows was affected
  • Use GString parameters or named parameters – never concatenate user input into SQL strings
  • Wrap multi-step updates in withTransaction for atomicity
  • Use soft deletes in production when you need audit trails or data recovery
  • Test your WHERE clauses with a SELECT first before running UPDATE or DELETE
  • Use withBatch() when updating many rows for better performance

DON’T:

  • Run DELETE FROM table without a WHERE clause unless you truly intend to remove all rows
  • Ignore the return count – it’s your safety net for catching mismatched conditions
  • Update or delete in a loop one row at a time when a single SQL statement with a proper WHERE clause would work
  • Forget to close the Sql instance – use sql.close() or Sql.withInstance

Performance Considerations

When it comes to Groovy SQL update and delete performance, a few patterns can make a big difference:

  • Batch over individual statements – A single withBatch() call with 1000 updates is dramatically faster than 1000 individual executeUpdate() calls. The batch size (first parameter) controls how many statements are sent to the database at once. A size of 50-100 is usually a good starting point.
  • Use database-side logic – Expressions like salary = salary * 1.10 run on the database server, which is far faster than reading each row into Groovy, computing the new value, and writing it back.
  • Index your WHERE columns – If your WHERE clause filters on a column without an index, the database has to scan every row. For large tables, this turns a millisecond operation into a multi-second one.
  • TRUNCATE for full-table wipes – If you’re deleting all rows, TRUNCATE is orders of magnitude faster than DELETE.
  • Limit large deletes – Deleting millions of rows in a single statement can lock the table and consume massive amounts of transaction log space. Consider deleting in chunks: DELETE FROM table WHERE id IN (SELECT id FROM table WHERE condition LIMIT 1000).

Common Pitfalls

Pitfall 1: Forgetting the WHERE Clause

Problem: Running an UPDATE or DELETE without a WHERE clause modifies every single row in the table.

Pitfall 1: Missing WHERE Clause

// DANGER: This updates EVERY employee!
sql.executeUpdate("UPDATE employees SET department = 'Fired'")

// DANGER: This deletes EVERY employee!
sql.executeUpdate("DELETE FROM employees")

// SAFE: Always have a WHERE clause
sql.executeUpdate("UPDATE employees SET department = 'Fired' WHERE name = 'Bob'")

Solution: Always test your WHERE clause with a SELECT first. Run SELECT COUNT(*) FROM employees WHERE ... before running the actual UPDATE or DELETE. This lets you verify the row count before making changes.

Pitfall 2: String Concatenation Instead of Parameters

Problem: Building SQL by concatenating strings – this opens you to SQL injection attacks.

Pitfall 2: SQL Injection Risk

def userInput = "Alice'; DROP TABLE employees; --"

// BAD: String concatenation - SQL injection vulnerable!
// sql.executeUpdate("DELETE FROM employees WHERE name = '" + userInput + "'")

// GOOD: GString parameterization - safe!
sql.executeUpdate("DELETE FROM employees WHERE name = ${userInput}")

// ALSO GOOD: Named parameters
sql.executeUpdate("DELETE FROM employees WHERE name = :name", [name: userInput])

Solution: Always use GString interpolation or named parameters. Groovy’s Sql class automatically creates PreparedStatements when you use GStrings, so your variables are safely bound as parameters – not concatenated into the SQL string.

Pitfall 3: Not Using Transactions for Multi-Step Operations

Problem: Running multiple related updates without a transaction, so a failure halfway through leaves your data in an inconsistent state.

Pitfall 3: Missing Transaction

// BAD: If the second update fails, the first one already committed
sql.executeUpdate("UPDATE accounts SET balance = balance - 500 WHERE id = 1")
sql.executeUpdate("UPDATE accounts SET balance = balance + 500 WHERE id = 2")

// GOOD: Wrap in a transaction
sql.withTransaction {
    sql.executeUpdate("UPDATE accounts SET balance = balance - 500 WHERE id = 1")
    sql.executeUpdate("UPDATE accounts SET balance = balance + 500 WHERE id = 2")
}

Solution: Wrap related updates in sql.withTransaction { ... }. If any statement inside the block throws an exception, all changes are rolled back. For more on transactions, see our upcoming Groovy SQL Transactions and Batch Operations post.

Conclusion

Groovy SQL update and delete operations are simple once you master the executeUpdate() method. The combination of GString parameterization, named parameters, batch processing, and built-in transaction support makes Groovy one of the most pleasant languages for database manipulation on the JVM.

From quick data cleanup scripts to migration pipelines and soft delete logic, Groovy’s Sql class has you covered with minimal boilerplate and maximum readability.

If you haven’t already, make sure to check out our Groovy SQL Database Connection guide for setting up your database connection, and Groovy SQL Read and Query Data for the SELECT side of things. Next up, we’ll cover Groovy SQL Transactions and Batch Operations for handling complex multi-statement workflows safely.

Summary

  • executeUpdate() handles UPDATE, DELETE, INSERT, and DDL statements and returns the affected row count
  • Use GString interpolation for safe, parameterized queries – Groovy auto-creates PreparedStatements
  • Named parameters (:name syntax with a Map) make complex queries more readable
  • Use withBatch() for bulk updates – dramatically faster than individual calls
  • Prefer soft deletes (active flag) over hard deletes in production for data recovery
  • Always check the return count and test your WHERE clause with SELECT first
  • Wrap multi-step operations in withTransaction for atomicity

If you also work with build tools, CI/CD pipelines, or cloud CLIs, check out Command Playground to practice 105+ CLI tools directly in your browser — no install needed.

Up next: Groovy SQL Transactions and Batch Operations

Frequently Asked Questions

How do I update a row in Groovy SQL?

Use the executeUpdate() method from groovy.sql.Sql. Pass your UPDATE SQL statement as a GString with embedded variables: sql.executeUpdate("UPDATE employees SET salary = ${newSalary} WHERE name = ${name}"). Groovy automatically creates a safe PreparedStatement. The method returns the number of rows affected.

How do I delete rows in Groovy SQL?

Call executeUpdate() with a DELETE statement: sql.executeUpdate("DELETE FROM employees WHERE id = ${id}"). Always include a WHERE clause unless you intentionally want to remove all rows. The return value tells you how many rows were deleted. For safety, test your WHERE clause with a SELECT first.

What is the difference between DELETE and TRUNCATE in Groovy SQL?

DELETE is a DML statement that removes rows one by one, supports WHERE clauses, can be rolled back in a transaction, and returns a row count. TRUNCATE is a DDL statement that removes all rows instantly, resets auto-increment counters, and cannot be rolled back in most databases. Use DELETE for selective removal and TRUNCATE for full table cleanup in tests.

How do I prevent SQL injection in Groovy update and delete statements?

Use GString interpolation or named parameters – never concatenate user input into SQL strings. When you pass a GString to executeUpdate(), Groovy creates a PreparedStatement with ? placeholders and binds values safely. For named parameters, use the :paramName syntax with a Map: sql.executeUpdate("DELETE FROM users WHERE id = :id", [id: userId]).

What is the soft delete pattern in Groovy?

Instead of physically deleting rows with DELETE, you add an active (or deleted) boolean column and update it to FALSE: sql.executeUpdate("UPDATE employees SET active = FALSE WHERE id = ${id}"). All SELECT queries then include WHERE active = TRUE. This preserves data for auditing and allows easy restoration by setting active back to TRUE.

Previous in Series: Groovy SQL Stored Procedures

Next in Series: Groovy SQL Transactions and Batch Operations

Related Topics You Might Like:

This post is part of the Groovy & Grails Cookbook series on TechnoScripts.com

Rahul

GroovyGroovy Database

databasedeleteGroovyH2sqltutorialupdate

FacebookXPinterestRedditLinkedinWhatsappTelegramEmailShortlink

RahulAuthor posts

Avatar for Rahul

Rahul is a passionate IT professional who loves to sharing his knowledge with others and inspiring them to expand their technical knowledge. Rahul's current objective is to write informative and easy-to-understand articles to help people avoid day-to-day technical issues altogether. Follow Rahul's blog to stay informed on the latest trends in IT and gain insights into how to tackle complex technical issues. Whether you're a beginner or an expert in the field, Rahul's articles are sure to leave you feeling inspired and informed.

Related Posts ...

No comment

Leave a Reply

Your email address will not be published. Required fields are marked *