SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: UPSERT was added to SQLite in version 3.24.0 6/4/18  (Read 278 times)

cj

  • Master Geek
  • ****
  • Posts: 686
    • View Profile
UPSERT was added to SQLite in version 3.24.0 6/4/18
« on: June 04, 2018, 05:35:37 PM »

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



« Last Edit: June 04, 2018, 06:14:08 PM by cj »
Logged
CJ

Bern Ertl

  • Master Geek
  • ****
  • Posts: 496
  • Excellent
    • View Profile
    • InterPlan Systems
Re: UPSERT was added to SQLite in version 3.24.0 6/4/18
« Reply #1 on: June 05, 2018, 08:07:04 AM »

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

cj

  • Master Geek
  • ****
  • Posts: 686
    • View Profile
UPSERT with binding, count ? marks for needed slBuildBindDat statements
« Reply #2 on: June 06, 2018, 07:40:10 AM »

'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
« Last Edit: June 06, 2018, 07:51:43 AM by cj »
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 591
    • View Profile
    • Software Development Company
Re: UPSERT was added to SQLite in version 3.24.0 6/4/18
« Reply #3 on: June 10, 2018, 10:53:52 AM »

Quote
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.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet