SQL | Best practices to build SQL update query with not null values only

Question

Looking for best practices to build an update query with not null values.

Below the my implementation where passed parameter firstName, middleName, lastName, address can be null. I want to build an update query with not null values only. I have used multiple if condition for not null and comma separator.

public void updatePerson(String id, String firstName, String middleName, String lastName, String address) {
    StringBuilder query = new StringBuilder("UPDATE PERSON SET ");
    MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
    mapSqlParameterSource.addValue("id", id);
    boolean columnSepartor = false;

    if (firstName != null) {
        query.append(" FIRST_NAME =:firstName");
        mapSqlParameterSource.addValue("firstName", firstName);
        columnSepartor = true;
    }

    if (middleName != null) {
        query = columnSepartor ? query.append(", MIDDLE_NAME =:middleName") : query.append(" MIDDLE_NAME =:middleName");
        mapSqlParameterSource.addValue("middleName", middleName);
        columnSepartor = true;
    }

    if (lastName != null) {
        query = columnSepartor ? query.append(", LAST_NAME =:lastName") : query.append(" LAST_NAME =:lastName");
        mapSqlParameterSource.addValue("lastName", lastName);
        columnSepartor = true;
    }

    if (address != null) {
        query = columnSepartor ? query.append(", ADDRESS =:address") : query.append(" ADDRESS =:address");
        mapSqlParameterSource.addValue("address", address);
    }

    query.append(" WHERE ID  =:id");
    namedParameterJdbcTemplate.update(query.toString(), mapSqlParameterSource);
}

Please suggest best practices to target such use case. Thanks in advance.

So, you want to update only those columns in a table that are currently not null for a given id? Is that what you mean? — Kaushik Nayak
– Kaushik Nayak, Commented Apr 13, 2018 at 6:51
To "build an update query" in oracle, especially if we don't know the column names, would require dynamic sql. But if you just want to run an update statement, the method in the answers here will definitely be efficient. — Kaushik Nayak
– Kaushik Nayak, Commented Apr 13, 2018 at 7:55

Ronnis · Accepted Answer · 2018-04-13 07:26:44Z

1

This would be my first choice of implementation for that requirement. It's small, it's easily understood and it will perform just as well for the vast majority of applications.

The coalesce expression returns the first non-null value. In this case it returns the new value (if there is one), else it returns the existing value from the column.

update person
   set first_name  = coalesce(:firstName,  first_name)
      ,middle_name = coalesce(:middleName, middle_name)
      ,last_name   = coalesce(:lastName,   last_name)
      ,address     = coalesce(:address,    address)
 where id = :id

answered Apr 13, 2018 at 7:26

Ronnis

12.9k3 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FDavidov · Accepted Answer · 2018-04-13 07:27:45Z

0

You can build an SQL command as follows:

UPDATE <your table>
  SET FirstName = NVL(PassedFirstName , FirstName) ,
      MidName   = NVL(PassedMidName   , MidName  ) , 
      LastName  = NVL(PassedLastName  , LastName ) ...
 WHERE...

which means, for any passed null value, update the record with the original value.

answered Apr 13, 2018 at 7:27

FDavidov

3,7556 gold badges27 silver badges72 bronze badges

Collectives™ on Stack Overflow

SQL | Best practices to build SQL update query with not null values only

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related