SQL update from an array
Hi guys,
Ok i have an array which collects 'quantity' values from the previous jsp and the aim is to update my DB with these values. The updating of the specific records is working fine (in the sense that it finds the right recorded to update.
However the problem is that it updates all the records with the last value in the array. for example, if the values 1,2,3 go into the array i think it is updating them ALL with 1, then all with 2 then all with 3. the intension is to have the first record with value 1, the second with 2 and third with 3 but it seems to be running through all and obviously 3 is the last number in the array so thats all im seeing in the DB.
I can see that the problem lies within that loop but cannot figure out how to update the records with the different array values.
here's my code:
String[] vals = request.getParameterValues("Quantity");
Statement statement = null;
ResultSet rs = null;
try{
PreparedStatement ps = connection.prepareStatement("UPDATE SelectedItems SET Quantity=? WHERE LotID=(SELECT max(LotID) FROM SelectedItems)");
for (int i = 0; i<vals.length; i++){
ps.setString(1, vals[ i ]);
ps.executeUpdate();
}
}
catch (Exception e)
{
e.printStackTrace();
System.err.println(
"TS_ERROR: Problem with inserting selected items");
}
....
any suggestions?
Thanks
Message was edited by:
haggard17>
[1504 byte] By [
haggard17a] at [2007-11-27 10:48:16]
# 2
This will always give you the same value. For example if the records have LotID values of 1, 2, 3, 10, 35, 42 it will always select the value 42.
(SELECT max(LotID) FROM SelectedItems)
If I change your query to reflect this fact:
UPDATE SelectedItems SET Quantity=? WHERE LotID=42
This will therefore ALWAYS update all rows having LotID = 42. If I call it three times, with the parameter set to 10, 20, 30, ALL the rows having LotID = 42 will have their Quantity field set to a value of 30.
I presume that's not what you wanted. What did you want?
(edit: because I'm having a typo day)
# 3
the table consisits of lotID's and a Quantity. The values in the array are to be placed in the 'quantity' column not the lotID (as these are already filled).
So...it pulls out the most recent records (based on the highest LotID) and for however many records there are, it will have a matching equivalent in the array in which those values are updated to the records one after another.
e.g. If the array has (1,2,3) the first record should be updated with 1, second with 2 and so on..
# 4
Currently you're saying:
Update all the records with LotID = 1 so that Quantity = 1.
Update all the records with LotID = 1 so that Quantity = 2.
Update all the records with LotID = 1 so that Quantity = 3.
To do what you want you need to be able to uniquely identify rows - you haven't said WHICH row you want to update with WHICH value.
You need to have: A primary key (to uniquely identify the row you want to update) and an ordering column (otherwise you'll get the set of rows in any old order).
