SQLite: decrement value if exists AND if not 0 else set default value

Question

I'm a totally newbie in SQLite and also SQL is not my strength, so i could need help. I have a table like this:

--------------------------------------
try
--------------------------------------
id:INTEGER PRIMARY KEY AUTO_INCREMENT,
ip:VARCHAR(30),
number:INTEGER DEFAULT 10,
bdate:DATE

For what do i need this kind of table? I want to "ban" a person for 24 hours, if he gets 10 times false credential error. I just want to decrement the attribute number. So if the person types false logging information for first time, a new entry should be create with 10 as default number. This is what i have so far:

INSERT OR REPLACE INTO try(id, ip, number, bdate) VALUES(COALESCE((SELECT id FROM try WHERE ip="172.16.1.1"), NULL),"172.16.1.1" ,{DON'T KNOW WHAT TO FILL UP}, 06092013);

I'm almost done, except de decrement of the value. I thought about something like that:

COALESCE((SELECT number FROM try WHERE ip="172.16.1.1"), 10)

Any idea how to decrement the result from the selection only if its NOT 0(like the SET method from SQL)? If it's 0, a new statement should be done(like add new entry to table ban ip).

EDIT:

I got it with the decrement. Look my answer for the solution.

Now i want to use a if else statement. I googled and found the CASE WHEN statement, but i don't know how to use it. What do I do wrong?

SELECT number FROM try WHERE ip="172.16.1.1" AS number,
CASE WHEN number > 0

THEN
(INSERT OR REPLACE INTO try(id, ip, number, bdate) VALUES(COALESCE((SELECT id FROM try WHERE ip="172.16.1.1"), NULL),"172.16.1.1" ,COALESCE(number-1, 10), 06092013)

ELSE
{DO SOMETHING ELSE}

END;

Answer 1

I got it with the decrement on my own:

INSERT OR REPLACE INTO try(id, ip, number, bdate) VALUES(COALESCE((SELECT id FROM try WHERE ip="172.16.1.1"), NULL),"172.16.1.1" ,COALESCE((SELECT number FROM try WHERE ip="172.16.1.1")-1, 10), 06092013;

Answer 2

SQLite is an embedded database and is not designed to do program logic directly in SQL. There is no IF/ELSE statement, or any other statement that behaves like that. You should do the logic in whatever language you are using to access the database:

db.execute("BEGIN")
cursor = db.query("SELECT id, number FROM try WHERE ip = '172.16.1.1'")
if cursor.empty or cursor.number = 0:
    db.execute("INSERT INTO try VALUES(NULL, '172.16.1.1', 10, '2013-09-06')")
else:
    db.execute("UPDATE try SET number = ? WHERE id = ?",
               [cursor.number - 1, cursor.id])
db.execute("COMMIT")

If you really want to do this in an incomprehensible SQL statement, try this:

INSERT OR REPLACE INTO try(id, ip, number, bdate)
VALUES ((SELECT id
         FROM try
         WHERE ip = '172.16.1.1'
           AND number > 0),
        '172.16.1.1',
        COALESCE((SELECT number
                  FROM try
                  WHERE ip = '172.16.1.1'
                    AND number > 0
                 ) - 1,
                 10)
        '2013-09-06'
       )