CODEWITHSUNDEEP
sqlarrayssqlite
Rodney Nart
Rodney Nart

Reputation: 121

Update all column rows with from average calculation in sql

I have an array that gets values from a column in my database. 

String sql1 = "SELECT COUNT(DISTINCT Total) AS Rank FROM class1 s1 JOIN 
class1 s2 ON (s1.Total <= s2.Total) GROUP BY s1.ID ORDER BY s1.Name ASC";

pst = conn.prepareStatement(sql1);
rs = pst.executeQuery();

ArrayList<String> marks = new ArrayList<>();

while (rs.next()) { 
    marks.add(rs.getString(1));
}   

String[] order = new String[marks.listIterator().nextIndex()];
order = marks.toArray(order);

This ranks values in 'Total column' and displays results in a computed column 'Rank'.

The values in the computed column are then stored in an array called 'order'.

So for example: order = [1,2,3] which is for all the 3 rows in the computed column rank...

I want to do is to update a column Position in the table with values from the arraylist, such that I have a result like :

| NAME  | SCORE 1 | SCORE 2 | TOTAL | POSITION | 
+-------+---------+---------+-------+----------+
| james |   10.0  |   24.0  |  34.0 |    1     | 
| jimmy |   10.0  |   20.0  |  30.0 |    2     | 
| josh  |   10.0  |   19.0  |  29.0 |    3     |

but when I try, this is how my table looks like: 

| NAME  | SCORE 1 | SCORE 2 | TOTAL  | POSITION  | 
--------+---------+---------+--------+-----------+
| james |   10.0  |   24.0  |  34.0  |   [1,2,3] | 
| jimmy |   10.0  |   20.0  |  30.0  |   [1,2,3] | 
| josh  |   10.0  |   19.0  |  29.0  |   [1,2,3] |

This is the code I wrote to update the 'Position column' with the array.. 

String sql1="SELECT COUNT(DISTINCT Total)AS Rank from class1 s1 JOIN class1 
s2 on(s1.Total<=s2.Total) GROUP BY s1.ID order by s1.Name ASC";
pst = conn.prepareStatement(sql1);
rs = pst.executeQuery();

ArrayList<String> marks = new ArrayList<>();

while (rs.next()) { 
    marks.add(rs.getString(1));
}

String[] order = new String[marks.listIterator().nextIndex()];
order = marks.toArray(order);

String sql = "update class1 set Position ='"+Arrays.toString(order)+"'"; 
pst = conn.prepareStatement(sql);
pst.execute();

Please any help will be very well appreciated. Thank you.

Upvotes: 0

Views: 58

Answers (2)

Clement Amarnath
Clement Amarnath

Reputation: 5466

You want to set individual values for different rows but the statement

String sql = "update class1 set Position ='"+Arrays.toString(order)+"'";

will be setting the same value for the Position column for all the rows, here Arrays.toString(order) will be giving the value [1,2,3] and hence the final result of the table has all the rows having value [1,2,3] for column position.

To update individual rows correctly we need to pass on unique identifier for each rows to update the Position.

Pseudo code:

Together with the Rank count in your select query, select unique id for each row, with the help of unique_id for each row only we will be able to update the exact row in the table. Please modify the column names matching to your table declaration

String sql1 = SELECT COUNT(DISTINCT s2.Total) AS Rank from class1 s1 
JOIN class1 s2 on (s1.Total<=s2.Total)
GROUP BY s1.ID order by s1.Name ASC;

pst = conn.prepareStatement(sql1);
rs = pst.executeQuery();

String sql = "";
while (rs.next()) { 
    String sql = "update class1 set Position ='"+rs.getString(2)+"'"+"WHERE unique_id="+rs.getString(1);
    pst = conn.prepareStatement(sql);
    pst.execute();
}

Upvotes: 1

Rodney Nart
Rodney Nart

Reputation: 121

many thanks to you Clement Amarnath....your solution was right.. i just had to change the values in your update statement from 1 to 2 and from 0 to 1. the initial one was :

String sql = "update class1 set Position ='"+rs.getString(1)+"'"+"WHERE unique_id="+rs.getString(0); 
    pst = conn.prepareStatement(sql);
    pst.execute();

it seems sqlite3 has a different way of indexing their default row.. so i changed it this way... 

String sql = "update class1 set Position ='"+rs.getString(2)+"'"+"WHERE unique_id="+rs.getString(1); 
    pst = conn.prepareStatement(sql);
    pst.execute();

Thank you very much. may you stay blessed..

Upvotes: 0

Related Questions