Mysql select and update column in one query

Question

Is possible to merge my following queries into one? I'm using mysql as my database.

String qry = "SELECT id FROM customers WHERE completed=false AND server=?";
ps = connection.prepareStatement(qry);
ps.setString(1,getServerId());
rs = ps.executeQuery();
final Set<Long> ids = new HashSet<>();
while (rs.next()) {
    ids.add(rs.getLong(1));
}
qry = "";
for (long l : ids) {
     qry += "UPDATE customers SET completed=true WHERE id = "+l+"; ";
}

... execute query, close streams, and do some application logic with ids from database...

Felype · Accepted Answer · 2015-03-24 16:46:48Z

2

You can just

UPDATE customers SET completed=true WHERE completed=false AND server=?

Edit: You provided the information saying you need to actually use the Ids. Select normally then build a string in the format MySql understands as a "list", it will certainly be faster than multiple update queries and will cost less bytes to send over the network. Here is the snippet:

String qry = "SELECT id FROM customers WHERE completed=false AND server=?";
ps = connection.prepareStatement(qry);
ps.setString(1,getServerId());
rs = ps.executeQuery();
final Set<Long> ids = new HashSet<>();
while (rs.next()) {
    ids.add(rs.getLong(1));
}
qry = "";
if(ids.size()>0) {
    StringBuilder idsAsString = new StringBuilder();
    for(int i = 0; i < ids.size(); i++) {
        idsAsString.append(ids.get(i));
        if(i < ids.size()-1)
            idsAsString.append(",");
    }
    qry = "UPDATE customers SET completed=true WHERE id IN ("+idsAsString+"); ";
}

instead of building multiple UPDATE statements in a for loop.

edited Mar 24, 2015 at 16:46

answered Mar 24, 2015 at 15:55

Felype

3,1362 gold badges27 silver badges36 bronze badges

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

1 Comment

user2749903 Over a year ago

well i need to do some application logic with ids, i need select statement in the query.

Collectives™ on Stack Overflow

Mysql select and update column in one query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related