I have a movement table and I want you to re-calculate when you update a past record:
I have these records in this table (the black line closes a data that does not have to see):
I am editing the first regitro. In the form I would be sending:
CantMov = 10
valingresa = 12,500.00 (This value is the same for valStockI)
and now, as before the data Cantmov was 20 the rest that the one that I have just entered, of the same way the value
CantMov = 20 - 10 (This 10 would be subtracting all stockActual of the following records)
valingresa = 25,000.00 - 12,500.00 (This 12,500.00 would be subtracting all valStockI from the following records)
I'm trying to make it look like this:
<table border=1>
<thead>
<tr>
<td>cantMov</td>
<td>valoringresa</td>
<td>stockActual</td>
<td>valStockI</td>
</tr>
</thead>
<tbody>
<tr>
<td>10</td>
<td>12,500.00</td>
<td>10</td>
<td>12,500.00</td>
</tr>
<tr>
<td>10</td>
<td>12,500.00</td>
<td>20</td>
<td>25,000.00</td>
</tr>
<tr>
<td>10</td>
<td>12,500.00</td>
<td>10</td>
<td>12,500.00</td>
</tr>
</tbody>
</table>But I miss the column valStockI
In all the regitros it gives me the same value: 12,500.00
I put some sout in the consultations and showed me this:
(First round)
update_valStockI : UPDATE movimientos set valStockI='25,000.00 ' where idMovimiento > 2 and idStockI= 2 and valStockI ='37,500.00'
(Second round)
update_valStockI : UPDATE movimientos set valStockI='12,500.00 ' where idMovimiento > 2 and idStockI= 2 and valStockI ='25,000.00'
I know where the error is but I do not know how to fix it
The error is that in the first round it updates correctly but in the second as the data that it had 37,500.00 it became 25,000.00 it also updates it with the other registers converting it in 12,500.00
** The code I use is this: **
<!--SELECCIONO LOS VALORES A ACTUALIZAR-->
String query_valStockI = "SELECT valStockI FROM movimientos where idMovimiento > " + mod.getIdMovimiento() + " and idStockI= " + mod.getIdStockI() + "";
try{
Statement st_valorIngresar = con.createStatement();
ResultSet rs_valorIngresar = st_valorIngresar.executeQuery(query_valStockI);
while (rs_valorIngresar.next()) {
double total = Double.parseDouble(rs_valorIngresar.getString(1).replace(",", ""));
double totalfinal = total + valoringresaplus;
String totalfinal_ = nf.format(totalfinal).replace("$", "");
<!--rs_valorIngresar.getString(1) es igual a "37,500.00" del ejemplo de arriba-->
String update_valStockI = "UPDATE movimientos set valStockI='" + totalfinal_ + " ' where idMovimiento > " + mod.getIdMovimiento() + " and idStockI= " + mod.getIdStockI() + " and valStockI ='" + rs_valorIngresar.getString(1) + "' ";
try {
ps_valStockI = con.prepareStatement(update_valStockI);
ps_valStockI.execute();
} catch (SQLException e)
{
System.err.println(e);
return false;
}
}
}catch (SQLException e) {
System.err.println(e);
return false;
}If you need more information or did not understand something please let me know, I have no idea how to solve this problem