CODEWITHSUNDEEP
javasqlsql-server
Zephyr
Zephyr

Reputation: 10253

Is it possible to determine which rows are affected by SQL Server UPDATE?

I am using a Java program to update a SQL Server 2017 database. Currently, while testing my UPDATE statements, I am not committing the changes (connection.setAutoCommit(false)).

When I print out the number of rows affected, the count is much lower than expected (the UPDATE statements should be updating every row).

Here is some sample code that demonstrates the loop I am using to issue the UPDATE statements:

    // List of users that need to be updated
    List<User> updatedUsers = new ArrayList<>();

    // Connect to database
    Connection connection = null;
    connection = DataFiles.getServerConnection();
    connection.setAutoCommit(false);

    PreparedStatement preparedStatement = null;

    int rowsAffected = 0;
    final int batchSize = 1000;
    int count = 0;

    // Create an UPDATE statement for each record to be updated
    for (User user : updatedUsers) {

        StringBuilder sql = new StringBuilder(
                "UPDATE USERS SET\n"
        );

        sql.append("USER_TYPE=?,\n")
                .append("FIRST_NAME=?,\n")
                .append("LAST_NAME=?,\n")
                .append("EMAIL_ADDRESS=?\n");
        sql.append("WHERE USER_ID=?");

        // Fill each ?
        preparedStatement = connection.prepareStatement(sql.toString());

        preparedStatement.setString(1, user.getUserTypeId());
        preparedStatement.setString(2, user.getFirstName());
        preparedStatement.setString(3, user.getLastName());
        preparedStatement.setString(4, user.getEmailAddress());
        preparedStatement.setString(5, user.getUserId());
        preparedStatement.addBatch();

        // Submit in batches of 1000
        if (++count % batchSize == 0) {
            rowsAffected += IntStream.of(preparedStatement.executeBatch()).sum();
            preparedStatement.clearBatch();
        }
    }
    System.out.println(count);

    rowsAffected += IntStream.of(preparedStatement.executeBatch()).sum();
    preparedStatement.clearBatch();
    preparedStatement.close();

    System.out.println(rowsAffected + " rows affected!");

My WHERE statement should be matching a record for every pass of the loop, yet I am getting 33 rows affected instead of the 32,000 I am expecting.

Is there a way to return the rows affected? I want to compare the matches to those that did not match.

I have already tried adapting my loop to a SELECT statement with the same WHERE criteria and that returns all 32,000 records, so I'm confident that the records DO exist.

Upvotes: 1

Views: 555

Answers (1)

Deadron
Deadron

Reputation: 5289

You are creating a new PreparedStatement in every iteration of your loop. You need to queue up batch executions on the same preparedStatement object that you call executeBatch. Currently you are only ever executing the 1000th queued query.

StringBuilder sql = new StringBuilder(
    "UPDATE USERS SET\n"
);

sql.append("USER_TYPE=?,\n")
    .append("FIRST_NAME=?,\n")
    .append("LAST_NAME=?,\n")
   .append("EMAIL_ADDRESS=?\n");
sql.append("WHERE USER_ID=?");

preparedStatement = connection.prepareStatement(sql.toString());

for (User user : updatedUsers) {
        preparedStatement.setString(1, user.getUserTypeId());
        preparedStatement.setString(2, user.getFirstName());
        preparedStatement.setString(3, user.getLastName());
        preparedStatement.setString(4, user.getEmailAddress());
        preparedStatement.setString(5, user.getUserId());
        preparedStatement.addBatch();

        // Submit in batches of 1000
        if (++count % batchSize == 0) {
            rowsAffected += IntStream.of(preparedStatement.executeBatch()).sum();
            preparedStatement.clearBatch();
        }
}

For determining affected rows (In sql server 2005+) you can use the OUTPUT clause in your SQL to have the database return affected row data. Article

Upvotes: 1

Related Questions