How to Increment Integer Columns Value by 1 in SQL

Increment column value by one

A better approach is to use an updatable CTE:

DECLARE @id INT;
SET @id = 3507571;

with toupdate as (
      select t1.*, row_number() over (order by field_number) as seqnum
      from table1 t1
     )
update toupdate
    set field_number = @id + seqnum;

Increment value from 1 to onward based on other field in SQL

A simple row number should work for you here:

SELECT id,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS Result
FROM t1;

You tagged both MySQL and SQL Server. Do note that they are completely different products but the same should work in both. MySQL introduced ROW_NUMBER() in 8.0.

Toward HansG's comment,

SELECT id,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS Result
FROM t1;

will give you the random ordering mentioned.

Incrementing integer value in a column in a specific row

Optimized approach: just update/increment total_hits column value for a particular url_visited value:

(considering urls is a target table name)

def update_url_hits(url):
   sql_query = "UPDATE urls SET total_hits = total_hits + 1 WHERE url_visited = %"
   cursor.execute(sql_query, (url,))
   connection.commit()

In Mysql, how to fill a column with incremental integers?

If you don't mind the sorting of your columns you can DROP the planning_id and add it again now as Primary Key and with auto_increment like :

ALTER TABLE planning DROP COLUMN planning_id, 
ADD COLUMN planning_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, 
ADD PRIMARY KEY (planning_id);

This way your planning_id will refresh their values in an incremental order (1, 2, 3, 4 and so on).

Check MySQL AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted

How to increment an integer based on a group in SQL

You can use row_number():

select . . .,
       row_number() over (partition by jl.CustomObjectName order by (select NULL)) as columnNumber

How to increment a column value by 1 for selected rows in postgres?

Your UPDATE query contains users instead of name.

demo: db<>fiddle

UPDATE Items set counter = counter + 1 WHERE name = 'Tony';

how do i increment a value of column with condition?

In your implementation, you did not check if the id already exists or not.

I updated your code with an SQL statement that performs insertion/update as you mentioned-

• in case the id exists then only the specific effect needs to be increased
• if id does not exist then a new row with that id needs to be added/inserted into the test table)

I also replaced the variable i (a list of two elements), with two variables named id and effect_no to improve the readability of the code. The if else statements are also not needed as the effect_no can be used/incorporated into the sql statement itself.

Following is the updated code:

id, effect_no = (1,3) # the returning value from your function
sql = f"""INSERT INTO test (id, effect1, effect2, effect3)
VALUES ({id}, 0, 0, 0)
ON DUPLICATE KEY UPDATE effect{effect_no} = effect{effect_no} + 1"""
db.execute_query(myDb, sql)

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Exception as err:
        print(f"Error: '{err}'")

Also, I found the INSERT ... ON DUPLICATE KEY UPDATE SQL statement here https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/#using-insert--on-duplicate-key-update

---

Related Topics