update different column values with specific value

a table has a column named 'score' with different values.


|  name  | contact  |  area  |  score |

|  james | +222451   | eastp|70      |

|  jimmy | +222451   | eestp | 80    |

|   k.josh | +222451  | ecstp | 50    |

|   L.john | +222451  | efstp | 60    |

I want to update all score values with a specific value. eg. update all score values with 10.

therefore value 70 will be 80

value 80 will be 90

value 50 will be 60

value 60 will be 70

please how do I write a code to achieve this.
wrote down this but all columns get changed to the same value. please help.

int reg = 10;

try

String sql1 = "select Score from db_table where ID=db_table.ID";
pst = con.prepareStatement(sql1);
rs = pst.executeQuery();
while(rs.next())

int ad = rs.getInteger("Score");
int fad = ad+reg;
String sql2 = "update db_table set Score='" + fad + "' where _ID=db_table.ID";
pst = con.prepareStatement(sql2);
pst.execute();


catch(SQLException | HeadlessException e)

JOptionPane.showMessageDialog(null,e);
finally

try

rs.close();
pst.close();
catch(Exception e)




but anytime is executed, the whole column values are replaced with the same value '10'. instead of each column value should rather be increased by 10. please help

2 Answers
2

Use the following query:

UPDATE db_table
SET score = score + 10


So, basically you don't need to SELECT all the scores first, and then manipulate them inside Java code, and use UPDATE one by one. Instead, change your try block in the Java code as follows:

SELECT

UPDATE

try

String sql1 = "UPDATE db_table Set score = score + " + String.valueOf(reg);
pst = con.prepareStatement(sql1);
rs = pst.executeQuery();



Try this:

int reg = 10;
try
String sql1="select Score from db_table where ID=db_table.ID";
pst=con.prepareStatement(sql1);
rs=pst.executeQuery();
while(rs.next())
int ad = rs.getInteger("Score");
int fad = ad+reg;
String sql2 = "update db_table set Score=Score + "+fad+" where _ID=db_table.ID";
pst=con.prepareStatement(sql2);
pst.execute();

catch(SQLException | HeadlessException e)
JOptionPane.showMessageDialog(null,e);
finally
try
rs.close();
pst.close();

catch(Exception e)



Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.