How to increment an integer column's value by 1 in SQL

Practical application explained

Before we start changing things around, let's explore common scenarios when dealing with column increments.

To increment or not: Conditional incrementing

Sometimes, you don't want all rows to move forward. Here's how to increment only designated rows with a WHERE clause:

UPDATE YourTable 
-- Add the Robin Hood clause; take from the rich, give to the poor
SET YourColumn = YourColumn + 1 
WHERE YourOtherColumn < 100; -- Only for the rows where wealth is under 100


In this case, replace YourOtherColumn and 100 with your actual column and desired value.

Null riders: Addressing null values

Sometimes, a column might contain NULL values that need wrangling. Use COALESCE or ISNULL to set a standard value before incrementing:

UPDATE YourTable 
-- Consider this as memory foam; it adjusts to what's needed
SET YourColumn = COALESCE(YourColumn, 0) + 1; 


Starting from NULL, ending with 1. Does it sound like a motivational tale to you?

The cool kids' way: The += operator

If you're using modern SQL databases like SQL Server, you can use the shiny += syntax:

UPDATE YourTable 
-- Too cool for SQL
SET YourColumn += 1; 


Go the extra mile: Things to consider

Don't just increment values willy-nilly! Here are some pro tips to consider.

Backup: The time-travel device

Always backup data before updating your columns. It's like a time machine, taking you back when "oops!" moments happen.

IDENTITY: The self-serving column

Add an IDENTITY property to automate incrementation for new rows:

CREATE TABLE YourTable (
    IdColumn INT IDENTITY(1,1) PRIMARY KEY,
    ...
);


In the SQL world, IDENTITY(1,1) is just a fancy way of saying "start from 1, increment by 1".

Transactions: All or nothing

Wrap your UPDATE in a transaction to make sure everything is updated, or nothing changes:

BEGIN TRANSACTION;

UPDATE YourTable SET YourColumn = YourColumn + 1;

COMMIT;


If only our lives had this update-with-a-safety-net, right?