SQLITE - Setting default value on INSERT

Question

I need to make a statement that adds an user to my SQLite database if it doesn't exist, otherwise update its values and add 1 to his previous totalMessages.
So far I wrote this:

INSERT OR REPLACE INTO users (id, username, first, last, totalMessages) values (
'hisID',
'hisName',
'hisFirst',
'hisLast',
(SELECT totalMessages FROM users WHERE id = 'hisID') +1);

It works fine when the user already exists, but when it doesn't, the value of totalMessages is set to NULL, and I need it to be 0.

This is my users table:

CREATE TABLE IF NOT EXISTS users  (
id VARCHAR(255) PRIMARY KEY,
username VARCHAR(255), 
first VARCHAR(255),
last VARCHAR(255),
totalMessages INTEGER)

Can anybody tell me how to do it, please?

UPDATE: I tried using "DEFAULT '0'" and "DEFAULT 0" as Scott suggested, but it is still saving totalMessages as NULL.

You can see it and test is here.

Why does this need to be a single statement? – CL. May 25 '16 at 07:05 — CL., May 25 '16 at 07:05

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

Your table definition should include the default for the totalMessages column.

CREATE TABLE IF NOT EXISTS users  (
...
totalMessages INTEGER DEFAULT '0')

SQLite doesn't allow for altering columns when modifying the table, however there are some strategies to do this manually, which have been documented at: "SQLite Modify Column" and "SQLite - alter a table's column type?"

edited May 23 '17 at 11:52

Community

1
1

answered May 24 '16 at 21:54

Scott Swezey

2,147
2
18
28

I know SQLite isn't strict about the data it is given, so I'm fairly certain the default I listed as `'0'` will work, however I'm not sure if it could have just been `0` and if that would be better. – Scott Swezey May 24 '16 at 21:55
totalMessages is still NULL doing that, maybe because the returned value from the SELECT is null, I'm not sure – Alberto May 24 '16 at 23:27

score -1 · Answer 2 · answered May 25 '16 at 07:33

I think I found a solution to my problem that may not be the best but it works.

REPLACE INTO users (id, username, first, last, totalMessages) values (
'hisID',
'hisName',
'hisFirst',
'hisLast',
CASE WHEN (SELECT totalMessages FROM users WHERE id = 'hisID') IS NULL THEN
  0
ELSE
  (SELECT totalMessages FROM users WHERE id = 'hisID') +1
END);

If anybody has a better solution please answer.

SQLITE - Setting default value on INSERT

2 Answers2