CODEWITHSUNDEEP
javasqlsqlitejdbcsql-update
user3484582
user3484582

Reputation: 557

SQL statement to update a field only if the value is greater than the current one

So far, I have this statement:

String query = "UPDATE Score SET Points = "+scorecard[TOTAL][i]+" WHERE Name = "+players[i].getName()+" AND Lastname = "+players[i].getLastName()";

Any idea how I should go about it? I saw somewhere to use the CASE WHEN syntax but couldn't make it work.

Upvotes: 2

Views: 1148

Answers (1)

Jan
Jan

Reputation: 13858

You'd want

update score set points = ? where
      name = ? and lastname = ?
      and points < ?

As SQL. And please use a PreparedStatement.

try(PreparedStatement ps = con.prepareStatement("update score set " + 
     " points = ? where name = ? and lastname = ? and points < ?") {
  ps.setInt(1, scorecard[TOTAL][i]);
  ps.setString(2, players[i].getName());
  ps.setString(3, players[i].getLastName());
  ps.setInt(4, scorecard[TOTAL][i]);
  ps.executeUpdate(); 
}

Upvotes: 3

Related Questions