Member Avatar for kevin wood

is it possible to add a number to a number that is stored in mysql db without extracting the stored number first.

i have a count and i am updating the count periodically but i would like to know if i can update the count while it is in side the table still.

i am not asking about how to update a field completely but to just add the new value to the already stored number.

  • 2 Contributors
  • 3 Replies
  • 89 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

A few practical notes to complement 's implementation and 's suggestions.

A numeric increment can be done in-place with a single SQL statement (no prior SELECT). That single-statement update is atomic at the statement level; with InnoDB it uses row-level locking so concurrent increments do not lose updates (MyISAM uses table-level locks). For high-concurrency counters prefer InnoDB. See MySQL documentation on InnoDB locking for details: InnoDB row-level locking.

Column type and bounds matter. Use an integer type appropriate for expected volume and mark it UNSIGNED if negative values are impossible. Common ranges: TINYINT (1 byte), SMALLINT (2), MEDIUMINT (3), INT (4), BIGINT (8). Choose BIGINT UNSIGNED when counts may exceed 2^31−1 to avoid overflow.

Prefer modern client APIs and parameterized statements instead of the old mysql_* calls. When a row might not exist, an atomic create-or-increment can be done with INSERT ... ON DUPLICATE KEY UPDATE — this avoids a separate INSERT/SELECT/UPDATE race: INSERT ... ON DUPLICATE KEY UPDATE. Example using PDO prepared statements:

$sth = $pdo->prepare(
  'INSERT INTO email_count (id, sent_messages)
   VALUES (:id, :inc)
   ON DUPLICATE KEY UPDATE sent_messages = sent_messages + VALUES(sent_messages)'
);
$sth->execute([':id' => $id, ':inc' => (int) $increment]);

Operational tips: bind increments as integers, check affected rows to confirm an update, include a WHERE clause unless intentionally updating every row, wrap multiple related operations in a transaction, and consider a specialized counter store (Redis) if write rate is extremely high. For prepared-statement guidance see PDO prepared statements.

Recommended Answers

All 3 Replies

Member Avatar for nav33n 
update table set number=number+300 where condition;

You mean like this ?

Edit: Or are you talking about concatenation ?

update table SET number = concat( number, "00" ) WHERE condition; //this will concat 00 to the value in number field for that condition
commented: extremely helpful all the time +1
Member Avatar for kevin wood

yes sorry i worke it out before then got distracted with something else here is the code that i ended up with.

mysql_query("UPDATE email_count SET sent_messages = sent_messages + $i")

i have not got the where clause in there as this is the only data inside the table.

thanks again for your reply you is always very helpful.

Member Avatar for nav33n

Great :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.