How to ignore a column in SQL updates

September 28, 2018, at 12:40 PM

I have a program that looks through a User model and adds values to a prepared update statement if they are not null as follows

    PreparedStatement pst = conn.prepareStatement("UPDATE users SET name=?,email=?,pwd=?,avatar=?,sts=?,bio=?,country=? WHERE uuid=?");
    if( != null) pst.setString(1,;
    if( != null) pst.setString(2,;
    if(this.password != null) pst.setString(3, this.password);
    if(this.avatar != null) pst.setString(4, this.avatar);
    if(this.status != null) pst.setString(5, this.status);
    if( != null) pst.setString(6,;
    if( != null) pst.setString(7,;

The problem I am facing is that I can't have an undefined field in the prepared statement. What can I set fields I don't want to change equal to?

Answer 1

Add the parameters, even with NULL values. Then change the update to:

UPDATE users
    SET name = coalesce(?, name),
       email = coalesce(?, email),
       . . .
   WHERE uuid = ?;
Rent Charter Buses Company