Conditional update in SQLite

Question

Can I create in SQLite a statement like this?

update books set
(if number_of_pages > 0 then number_of_pages = number_of_pages - 1)
where book_id = 10

Is there a working syntax for this?

score 26 · Accepted Answer · answered Feb 26 '12 at 13:57

26

A CASE statement should work with the following syntax:

UPDATE
  books
SET number_of_page = CASE WHEN number_of_pages > 0 THEN (number_of_pages - 1) ELSE 0 END
WHERE whatever_condition

answered Feb 26 '12 at 13:57

Michael Berkowski

260,803
45
432
377

Pheonix · Answer 2 · 2012-02-26T14:10:50.320

4

Isnt that equal to this statement ?

update books set number_of_pages = number_of_pages - 1 where number_of_pages>0

Edit:

according to new statement :

update books set number_of_pages = number_of_pages - 1 where number_of_pages>0 and book_id = 10

edited Feb 26 '12 at 14:10

answered Feb 26 '12 at 13:59

Pheonix

6,003
5
27
48

@Phoenix Sure is, for this example. – Michael Berkowski Feb 26 '12 at 14:06
@Phoenix I extended the statement a little, but think you're right. – xralf Feb 26 '12 at 14:08
@ It's good solution. I would rather accept the other one because there is more clear semantics (what I want to do) when somebody reads my code. But has your solution some advantage (e.g. in performance)? – xralf Feb 26 '12 at 14:13

score 1 · Answer 3 · answered Feb 26 '12 at 14:29

If you just want to conditionally update some rows and leave others intact, this should do:

update books
set number_of_pages = number_of_pages - 1
where 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 books
set number_of_pages = number_of_pages - 1
where number_of_pages > 0 AND book_id = 10

update books
set number_of_pages = 0
where number_of_pages <= 0 AND book_id = 10

Or just use CASE as others have suggested.

Conditional update in SQLite

3 Answers3

Linked