Subtracting an integer in my db by 1. SQL

Question

I want to subtract the follower count of a user by 1, or increase it by but I don't want to do this:

   $sql = $conn->prepare('SELECT folllower_num FROM users WHERE user_id = ?');
   $sql->execute($user_id));
   //then grab the follower_num value, save it to a variable, and subtract 1 
   //from it and then:
   $sql = $conn->prepare('UPDATE users SET follower_num = ? WHERE user_id = 
   ?;');
   $sql->execute($newfollowercount, $user_id));

or something like that. To me this seems bad to do because while the value is saved into this code and then changed and put back into the database, someone else could have followed or unfollowed and then the number becomes incorrect. Is there another way I can do this?

Answer 1

SQL allows to update the number of rows at once:

UPDATE users
   SET follower_num = follower_num + 1
 WHERE user_id IN (1,2,3,4...)

WHERE clause can contain any conditions you want. All users that satisfy that conditions will be updated.

Answer 2

UPDATE users SET follower_ct = follower_ct + 1 WHERE user_id = ?

(or - 1)

Answer 3

In addition to Kondybas's answer.

You may increase and decrease the field values in one statement:

UPDATE users
SET follower_num = follower_num + CASE WHEN user_id IN (1,3,5...)
                                       THEN 1
                                       ELSE -1 
                                       END
WHERE user_id IN (1,2,3,4,5,...)

The ids list in WHERE defines what records must be updated (with any type of update).

The ids list in CASE defines what records must be increased. The rest of records to be updated (not included in this list but present in the list in WHERE) will be decreased.

Of course, the CASE statement (and assignment expression at all) may be more complex and it may include more than 2 conditions and updating alternatives.