UPSERT was added to SQLite in version 3.24.0 6/4/18

[cj]

SQLite added UPSERT equivalent today 6/4/2018 IN version 3.24.0

http://www.sqlite.org/lang_UPSERT.html

If an INSERT fails then an UPDATE to the same ROWID is attempted.
The update after a failing insert can fail if it is also a duplicate.

This links says it is the same as INSERT OR REPLACE INTO.
I think that may work, but it will delete all columns and then INSERT.
https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace


This example has 2 columns and it demonstrates the second column is not deleted, hurray!

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL sRecordSet AS STRING
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1 unique, c2)"
DO
  slexe "insert into t1 values('Hello, world',' am i deleted') on conflict(c1) do update set c1 = '*duplicate so insert timer="+FORMAT$(TIMER) + "*'"
  sRecordSet = ""
  slsel "select rowid,* from t1"
  DO WHILE slgetrow
    sRecordSet+= slf(1) + " " + slf(2) + " " + slf(3) + $CR
  LOOP
  IF MSGBOX(sRecordSet,%MB_YESNO,"Yes = insert       No = done") <> %IDYES THEN EXIT DO
LOOP
END FUNCTION

[Bern Ertl]

Thanks for the alert.  This new feature looks pretty powerful.

[cj]

'upsert.bas 6/6/2018
'create table if not exists t1(c1 text primary key,c2 blob)
'Insert into t1 values(?,?) ON conflict(c1) DO update SET c2=?
'Note: 3-question marks(?) so 3-slBuildBindDat statements
http://www.sqlite.org/lang_UPSERT.html

#INCLUDE "sqlitening.inc"

$BlobInsert = CHR$(0,"Insert",0)
$BlobUpdate = CHR$(0,"Update",0)

FUNCTION PBMAIN () AS LONG

LOCAL sInsertStatement AS STRING
LOCAL sBind AS STRING

slOpen "conflict.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1 text primary key,c2 blob)"

REDIM sCol(1 TO 2) AS STRING             '2 data columns
sCol(1)= "C1-Key"                        'c1 data
sCol(2)= $BlobInsert                     'c2 data
sBind  = slBuildBindDat(sCol(1),"T") +_  'bind c1
          slBuildBindDat(sCol(2),"B") +_  'bind c2
          slBuildBindDat($BlobUpdate)     'on conflict ...

sInsertStatement = "Insert into t1 values(?,?) ON conflict(c1) DO update SET c2=?"
slexebind sInsertStatement,sBind :ShowAll  'C1-Key,*Insert*
slexebind sInsertStatement,sBind :ShowAll  'C1-Key,"Update*
END FUNCTION

SUB ShowAll
LOCAL COL,cols,rows AS LONG
LOCAL srecordset,sdelimit AS STRING
sdelimit = ","
slsel "select * from t1"
cols = slGetColumnCount
DO WHILE slgetrow
  INCR rows
  FOR COL=1 TO cols
   sRecordSet+= slfx(COL) + sdelimit  'no encrption or compression so slf is enough
  NEXT
  sRecordSet = LEFT$(sRecordSet,LEN(sRecordSet)-LEN(sdelimit)) + $CR
LOOP
REPLACE $NUL  WITH "*" IN sRecordSet 'blob test includes CHR$(0)
? sRecordSet,,"Rows" + STR$(rows)
END SUB

[Fredrick Ughimi]

SQLite added UPSERT equivalent today 6/4/2018 IN version 3.24.0

Really cool feature. Need to experiment on this. If everything goes well I would implement this feature on my applications. Thanks CJ for bringing this to our notice.