update different column values with specific value

Question

| 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

Hi, you dont need to do select and then update. You can do it in single update. Check stackoverflow.com/a/52243173/2469308 Let me know if it works for you. thanks :) — Madhur Bhaiya
– Madhur Bhaiya, Commented Sep 9, 2018 at 9:41
Unrelated to the problem you are stating, please note that in your finally statement the second close wont be executed if the first one errors. This is a common pitfall that has been addressed in Java 8 with the introduction of try-with-resource-close — Fritz Duchardt
– Fritz Duchardt, Commented Sep 9, 2018 at 9:59

Madhur Bhaiya · Accepted Answer · 2018-09-09 09:57:41Z

1

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:

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

edited Sep 9, 2018 at 9:57

answered Sep 9, 2018 at 9:33

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

Comments

Fabricio Ardizon · Accepted Answer · 2018-09-09 09:51:13Z

0

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){}
}

answered Sep 9, 2018 at 9:51

Fabricio Ardizon

1065 bronze badges

Collectives™ on Stack Overflow

update different column values with specific value

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