Conditional update in SQLite
If you just want to conditionally update some rows and leave others intact, this should do:
update booksset number_of_pages = number_of_pages - 1where number_of_pages > 0 AND book_id = 10
If you want to update all rows (with book_id = 10
) to different values, you can use 2 statements with "opposite" conditions. Assuming 0 is the "other" value, that would look like this:
update booksset number_of_pages = number_of_pages - 1where number_of_pages > 0 AND book_id = 10update booksset number_of_pages = 0where number_of_pages <= 0 AND book_id = 10
Or just use CASE as others have suggested.