120

is it possible to increase a certain value in a table by a certain number without reading last value and afterwards updating it?

i.e. I have columns "product" and "quality": product: iLamp quality: 50

I want to increase(or decrease) quality by x. To achieve this I am first reading last value (50), increasing or decreasing it, and writing it back.

Is there a direct way to complete this task?

Ilya Suzdalnitski
  • 51,312
  • 50
  • 131
  • 167

1 Answers1

252

Example 1 (for all rows):

UPDATE product SET price = price + 50

Example 2 (for a specific row):

UPDATE product SET price = price + 50 WHERE id = 1

Example 3 (for specific rows):

UPDATE product SET price = price + 50 WHERE id IN [1, 2, 3]

Example 4 (generic):

UPDATE {table} SET {column} = {column} + {value} WHERE {condition}

Where:

  • {table} - table name
  • {column} - column name
  • {value} - a number by which column's value should be increased or decreased
  • {condition} - some condition if any
Konstantin Tarkus
  • 36,352
  • 14
  • 132
  • 118
  • 3
    FROM is an SQLite keyword? The docs don't seem to indicate it. http://www.sqlite.org/lang_update.html – Jason S Apr 13 '09 at 15:43
  • how can this be done in python with sqlite3? I need to update a col += 1 where in first column = ? – st.ph.n Feb 03 '16 at 21:25
  • @user3358205: Like the man says... `UPDATE table SET col = col + 1 WHERE first_column = ?` – Mumbleskates Feb 12 '16 at 07:58
  • To increment on [a list of criteria](http://stackoverflow.com/questions/9041476/combining-a-large-number-of-conditions-in-sqlite-where-clause), do something like `UPDATE Products SET Price = Price + 50 WHERE [ProductID] IN [1,3,56,78,44,23,8989,23]` – zelusp Oct 24 '16 at 22:18
  • 1
    @Konstantin is this possible to increment all row value of an column incrementally ? Say if existing rows are "R, S, T" then it should be 'R1, S2, T3'. Any suggestion ? – CoDe Jul 26 '17 at 11:34
  • @CoDe you would use `row_number() over(ORDER BY id)` for that – Konstantin Tarkus Sep 12 '21 at 14:32