CODEWITHSUNDEEP
javasqldatabaseoracle-database
Vinod
Vinod

Reputation: 396

How to update multiple rows of a table based on one value?

I have two columns in my table "res_allocation", one "ResName" and other "PID", "ResName" will have multiple values for a single "PID".

Is there a single query for updating values of "ResName" in multiple rows based on PID?

The new values for "ResName" comes dynamically i.e., its a user input. I use SQL database.

Upvotes: 1

Views: 4272

Answers (1)

Jaco Van Niekerk
Jaco Van Niekerk

Reputation: 4192

This has been adapted from code I already have... there may be an error or two, but the crux will work (it works in my code). Ideally things like this should be done with an ORM tool like Hiberante.

Basically you set up the batch update and then run statement.executeBatch() to do the actual update. You are returned with an int[] array with the results. You check these against a list of predefined constants to see what is going on. This is MUCH faster than simply executing each update separately. Furthermore, you can combine all the updates in one transaction, making rollbacks easier.

public void updateResNames(List<ResAllocationDTO> list) {
    String sql = "UPDATE res_allocation SET ResName = ? WHERE PID = ?";
    PreparedStatement statement = null;
    try {
        statement = connection.prepareStatement(sql);
        for (ResAllocationDTO dto : list) {
            statement.setString(1, dto.getResName());
            statement.setString(2, dto.getPID());
            statement.addBatch();
        }
        int[] result = statement.executeBatch();
        for (int i = 0; i < result.length; i++) {
            if (result[i] == PreparedStatement.EXECUTE_FAILED) {
                throw new SQLException(String.format("Entry %d failed to execute in the batch insert with a return code of %d.", i, result[i]));
            }
        }
        commit();
    } catch (SQLException e) {
        logger.error(LoggerCodes.DATABASE_ERROR, e);
        rollback();
        throw new RuntimeException(e);
    } finally {
        close(statement);
    }
}

commit(), close() and rollback() looks like this:

public void close(PreparedStatement statement) {
    try {
        if (statement != null && !statement.isClosed())
            statement.close();
    } catch (SQLException e) {
        logger.debug(LoggerCodes.TRACE, "Warning! PreparedStatement could not be closed.");
    }
}

protected void commit() {
    try {
        if ((connection != null) && !connection.getAutoCommit()) {
            connection.commit();
        }
    } catch (SQLException e) {
        logger.debug(LoggerCodes.TRACE, "Warning! ResultSet could not be closed after commit.");
    }
}

protected void rollback() {
    try {
        if ((connection != null) && !connection.getAutoCommit()) {
            connection.rollback();
        }
    } catch (SQLException e) {
        logger.debug(LoggerCodes.TRACE, "Warning! ResultSet could not be closed after rollback.");
    }
}

I hope this helps you! Good luck and happy coding!

Upvotes: 1

Related Questions