Introduction

To increment a numeric column in MySQL, update the column based on its current value instead of reading the value in application code and writing it back later. The standard pattern is SET column = column + 1, which is simple, efficient, and safer under concurrent access than a separate read-then-write sequence.

The Basic Increment Pattern

Here is the canonical query:

UPDATE counters
SET view_count = view_count + 1
WHERE id = 42;

This tells MySQL to take the existing view_count value and add one directly in the database.

You can increment by any amount:

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 7;

The same idea works for decrementing as well:

UPDATE inventory
SET quantity = quantity - 1
WHERE sku = 'ABC-123';

Why This Is Better Than Reading First

A common but weaker pattern is:

1. SELECT view_count FROM counters WHERE id = 42
2. Add one in application code.
3. UPDATE counters SET view_count = ? WHERE id = 42

That approach is vulnerable to lost updates when multiple requests run at the same time. Letting MySQL perform the increment in one statement is usually the correct approach.

Increment Multiple Columns at Once

You can increment several fields in the same UPDATE.

1UPDATE game_stats
2SET wins = wins + 1,
3    points = points + 3,
4    matches_played = matches_played + 1
5WHERE player_id = 15;

This is useful when the update represents one logical event and those counters should change together.

Handle NULL Carefully

If a column can be NULL, adding to it keeps the result NULL.

UPDATE counters
SET view_count = view_count + 1
WHERE id = 42;

If view_count is NULL, the new value remains NULL.

Use COALESCE when needed:

UPDATE counters
SET view_count = COALESCE(view_count, 0) + 1
WHERE id = 42;

That treats a missing value as zero before incrementing.

Conditional Increments

You can combine increments with business rules in the WHERE clause.

1UPDATE coupons
2SET uses = uses + 1
3WHERE coupon_code = 'WELCOME10'
4  AND uses < max_uses;

After running the query, check the affected row count. If zero rows were updated, the coupon may already be exhausted or the code may not exist.

Transactions for Related Changes

If the increment must stay consistent with other updates, use a transaction.

1START TRANSACTION;
2
3UPDATE inventory
4SET quantity = quantity - 1
5WHERE sku = 'ABC-123' AND quantity > 0;
6
7UPDATE orders
8SET reserved = reserved + 1
9WHERE sku = 'ABC-123';
10
11COMMIT;

The increment syntax itself is the same, but a transaction helps keep several dependent changes together.

Common Pitfalls

The most serious mistake is forgetting the WHERE clause and incrementing every row in the table.

Another common mistake is doing the increment in application code after a separate SELECT, which increases the chance of race conditions.

Watch out for NULL values too. If the column was not initialized properly, column + 1 may not behave the way you expect.

Finally, make sure the column type is actually numeric. Increment logic belongs on integer, decimal, or other numeric columns, not text fields.

Summary

• Increment numeric columns in MySQL with SET column = column + 1.
• Let the database perform the increment instead of doing a read-then-write round trip.
• Use a WHERE clause so only the intended rows change.
• Use COALESCE if the column may be NULL.
• Wrap related updates in a transaction when the increment is part of a larger state change.