129

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

product quantity
iLamp 50

I want to increase (or decrease) quantity by x. I am first reading last value (50), increasing or decreasing it, and writing it back. Is there a direct way?

user4157124
  • 2,809
  • 13
  • 27
  • 42
Ilya Suzdalnitski
  • 52,598
  • 51
  • 134
  • 168

1 Answers1

271

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
  • 37,618
  • 14
  • 135
  • 121
  • 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
  • @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