Updating a value in a random row through java in a sql table

Question

Image of my table as it is when I start this program.

What I have is a mostly empty table and I am trying to assign a value to a fixed number of elements. The column I am trying to edit is "Geschlecht" and the number of rows I want to edit is "copyMaen" (~50.000 entries). I would like to only select the rows where the value of "Geschlecht" was NULL before and I would like to select the rows randomly.

I am using SQLite through a JDBC driver.

This is the first time for me working with sql. This is how I tried to do it.

                try {
        Statement stmt = DBController.connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT Geschlecht FROM individuen WHERE Geschlecht IS NULL;");
        PreparedStatement ps = DBController.connection.prepareStatement("UPDATE individuen");
        while (copyMaen != 0) {
            if (rs.getRowId((int) (Math.random() * ReadCSV.sumBev)) == null) {
                ps.setInt(2, 0);
                ps.executeUpdate();
                copyMaen--;
            }
        }
        rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

Obviosly this throws me Errors and I am not really sure how to go on from there. Could somebody point me in the right direction?

you want , like, while Geschlecht is null then update by Random values right ? — Vishal Gajera
– Vishal Gajera, Commented Apr 18, 2016 at 15:24
I want to set "Geschlecht" to 0 in a number of rows (int copyMean) but without changing any other values in the table. And it should only be updated if it was null before, of course. English is not my native language and I'm not even good at describing programming problems in my langue. Sorry! — UsefulUserName
– UsefulUserName, Commented Apr 18, 2016 at 15:43
What RDMBS are you using, as the answer will vary depending on this. For example, in SQL Server you could use RAND() to generate a randomi(ish) number. Then you could test this to see if you want to update the NULL value or not. — Richard Hansell
– Richard Hansell, Commented Apr 18, 2016 at 16:01
I am using SQLite through a JDBC Driver. Whould using RAND() look something like this? ResultSet rs = stmt.executeQuery("SELECT Geschlecht FROM individuen ORDER BY RAND() LIMIT 1;"); — UsefulUserName
– UsefulUserName, Commented Apr 18, 2016 at 16:26

UsefulUserName · Accepted Answer · 2016-04-30 09:40:05Z

1

For anybody interested this is the solution:

try {
        Statement stmt = DBController.connection.createStatement();
        String select = "SELECT ID FROM individuen WHERE Geschlecht is NULL ORDER BY RANDOM()" +
        " LIMIT " + Integer.toString(copyMaen);
        ResultSet rs = stmt.executeQuery(select);
        PreparedStatement ps = DBController.connection.prepareStatement("UPDATE individuen set Geschlecht  = ? WHERE ID = ?;");
        // rs.beforeFirst();
        int count = 0;
        while (rs.next()) {
            ps.setInt(1, 0);
            ps.setInt(2, rs.getInt(1));
            ps.addBatch();
            if (count%100==0) {
                System.out.println(count);
            }
            count++;
        }
        DBController.connection.setAutoCommit(false);
        ps.executeBatch();
        DBController.connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

edited Apr 30, 2016 at 9:40

answered Apr 19, 2016 at 12:40

UsefulUserName

6051 gold badge5 silver badges22 bronze badges

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

1 Comment

Vishal Gajera Over a year ago

Yes, this would be also fine.

Vishal Gajera · Accepted Answer · 2016-04-18 15:48:15Z

0

Try this,

Below update query is enough for update all row where Geschlecht is null.

try {
        Statement stmt = DBController.connection.createStatement();
        String updateTableSQL = "UPDATE individuen set Geschlecht  = ? where Geschlecht IS NULL";
        PreparedStatement preparedStatement = dbConnection.prepareStatement(updateTableSQL);
        preparedStatement.setString(1, "0"); // set zero where Geschlecht null found
        // execute update SQL stetement
        preparedStatement.executeUpdate();
} catch (SQLException e) {
        e.printStackTrace();
}

answered Apr 18, 2016 at 15:48

Vishal Gajera

4,2378 gold badges32 silver badges57 bronze badges

2 Comments

UsefulUserName Over a year ago

First of all, thanks a bunch. But this is not quite what I need. This updates all "null" values in "Geschlecht" to 0. But I would like only for a set amount to be changed. What would be great as well, were if the rows that are being changed would be selected randomly.

Vishal Gajera Over a year ago

Please, can you provide me a little bit more explanation ?

Collectives™ on Stack Overflow

Updating a value in a random row through java in a sql table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related