Upsert vs Insert or Replace on table WITHOUT ROWID

(1) By cj (sqlitening) on 2024-03-07 19:05:23 [link] [source]

Is it better to use Insert or Replace when there is no rowid?
It sure is easier to code.

slexe "create table t1(key text primary key,value text) without rowid"

skey   =  "one"
svalue =  "value1"

slexebind "Insert or Replace into t1 values(?,?)", binds(skey), binds(svalue)

slexebind "Insert into t1 values(?,?) ON conflict(key) DO update SET value=?", binds(skey)+ binds(svalue) + binds(svalue)

(2) By ddevienne on 2024-03-08 07:54:53 in reply to 1 [link] [source]

It's not a matter of WITHOUT ROWID or not.

It's a matter that insert or replace is in fact INSERT or DELETE + INSERT, there's no UPDATE.

While insert ... on confict do update ... really is INSERT or UPDATE.

For many, it doesn't matter much.

For others, like those with foreign keys and ON DELETE CASCADE,
then that implicit DELETE will be trouble, so should use the latter.

The change in DML can also affect triggers as well.

(3) By cj (sqlitening) on 2024-03-08 13:47:40 in reply to 2 [link] [source]

That makes sense.  Thank you.

create table filetable (Path text collate nocase,name collate nocase,thedata blob, primary key(name,path)) WITHOUT ROWID

sbind = binds(spath) + binds(sname) + bindb(sdata) + bindb(sdata) 
slexebind "insert into filetable values(?,?,?) on conflict(name,path) DO update Set thedata=?",sbind

Putting all source code files into a table from multiple directories.
99% of the time there will be no change to most of the files.

Would it make sense to compare source file to blob and skip if equal?
Works fine like it is.  Is it worth skipping an unneeded write?

(4) By cj (sqlitening) on 2024-03-08 18:34:22 in reply to 3 [source]

When done within a transaction the process with a thousands files is 
less than a second. So the question is does SQLite modify the files
even when nothing has changed? It is so fast.