如何在SQL表中更新行而非新增行（Groovy-SQL场景）

Got it, let's tackle this problem head-on. The core goal here is to update an existing record when RightCount changes (for the same machine and program) instead of creating a duplicate. First, we need a reliable way to identify which record to target—the combination of MachineName and ProgramName should act as your unique identifier (since you only want to update counts for the same machine running the same program).

Below are two practical approaches to implement this "upsert" (update + insert) behavior in Groovy-SQL:

1. Use Database-Native Upsert Syntax (Most Efficient) #

Most modern databases support atomic upsert operations that handle update/insert in a single query. This avoids race conditions in high-concurrency scenarios and cuts down on database round-trips.

Example for SQL Server (MERGE Statement) #

import groovy.sql.Sql

// Initialize your SQL connection (adjust JDBC details to match your database)
def sql = Sql.newInstance(
    "jdbc:sqlserver://your-db-server;databaseName=your-db",
    "db-username",
    "db-password",
    "com.microsoft.sqlserver.jdbc.SQLServerDriver"
)

// Sample data parsed from your text file (replace with your actual parsing logic)
def recordFromFile = [
    MachineName: "CNC-Machine-01",
    ProgramName: "AutoCut-V2",
    RightCount: 24,
    LeftCount: 18
]

// MERGE logic: update if the record exists, insert if it doesn't
def mergeQuery = """
MERGE INTO Machine AS target
USING (VALUES (?, ?, ?, ?)) AS source (MachineName, ProgramName, RightCount, LeftCount)
ON target.MachineName = source.MachineName AND target.ProgramName = source.ProgramName
WHEN MATCHED THEN
    UPDATE SET RightCount = source.RightCount, LeftCount = source.LeftCount
WHEN NOT MATCHED THEN
    INSERT (MachineName, ProgramName, RightCount, LeftCount)
    VALUES (source.MachineName, source.ProgramName, source.RightCount, source.LeftCount);
"""

// Execute the query with your parsed data
sql.execute(mergeQuery, [
    recordFromFile.MachineName,
    recordFromFile.ProgramName,
    recordFromFile.RightCount,
    recordFromFile.LeftCount
])

// Clean up the connection
sql.close()

Example for PostgreSQL (INSERT ... ON CONFLICT) #

For PostgreSQL, first add a unique constraint on (MachineName, ProgramName) to enforce uniqueness, then use this syntax:

def upsertQuery = """
INSERT INTO Machine (MachineName, ProgramName, RightCount, LeftCount)
VALUES (?, ?, ?, ?)
ON CONFLICT (MachineName, ProgramName) DO UPDATE
SET RightCount = EXCLUDED.RightCount, LeftCount = EXCLUDED.LeftCount;
"""

// Execute the query the same way as above
sql.execute(upsertQuery, [
    recordFromFile.MachineName,
    recordFromFile.ProgramName,
    recordFromFile.RightCount,
    recordFromFile.LeftCount
])

2. Check for Existence First (Compatible with All Databases) #

If your database doesn't support native upsert, you can first check if the record exists, then branch into update or insert logic. Note: this has a small risk of race conditions in high-traffic environments.

import groovy.sql.Sql

def sql = Sql.newInstance(/* your JDBC connection details */)
def recordFromFile = /* your parsed text file data */

// Check if the record already exists in the table
def recordExists = sql.firstRow(
    "SELECT COUNT(*) AS count FROM Machine WHERE MachineName = ? AND ProgramName = ?",
    [recordFromFile.MachineName, recordFromFile.ProgramName]
).count > 0

if (recordExists) {
    // Update the existing record with new count values
    sql.execute(
        "UPDATE Machine SET RightCount = ?, LeftCount = ? WHERE MachineName = ? AND ProgramName = ?",
        [recordFromFile.RightCount, recordFromFile.LeftCount, recordFromFile.MachineName, recordFromFile.ProgramName]
    )
} else {
    // Insert a brand new record
    sql.execute(
        "INSERT INTO Machine (MachineName, ProgramName, RightCount, LeftCount) VALUES (?, ?, ?, ?)",
        [recordFromFile.MachineName, recordFromFile.ProgramName, recordFromFile.RightCount, recordFromFile.LeftCount]
    )
}

sql.close()

Key Tips #

• Add a Unique Constraint: To avoid accidental duplicates and make your upsert logic bulletproof, add a unique constraint on (MachineName, ProgramName) in your SQL table. This ensures no two rows can have the same machine-program combination.
• Parameter Binding: Always use parameter placeholders (?) instead of string concatenation to avoid SQL injection and improve query performance.
• Batch Processing: If you're reading multiple records from the text file, use sql.withBatch() to process them in batches for better efficiency.

内容的提问来源于stack exchange，提问作者user9630935