Using The New UPSERT

[Fredrick Ughimi]

Hello,

I am trying my hands on the new UPSERT and I am having issues trying to use a string variable to represent my value.

Code Select Expand


#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
LOCAL sSubject AS STRING
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1 unique)"
sSubject = "Maths"

slEXE "insert into t1 values(sSubject) on conflict(c1) do nothing"

END FUNCTION         



Any push in the right direction would be very much appreciated.

Best regards.

[Bern Ertl]

slEXE "insert into t1 values(" + sSubject + ") on conflict(c1) do nothing"

[Fredrick Ughimi]

Hello Bern,

Thank you for your response. i tried some other options except except the one you provided.

Now I get a new error:

[cj]

>slEXE "insert into t1 values(" + sSubject  + ") on conflict(c1) do nothing"
  slEXE "insert into t1 values('" + sSubject + "') on conflict(c1) do nothing"

Attempt 20 inserts
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
LOCAL x,duplicates AS LONG
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1 unique)"

FOR x = 1 TO 10
  slEXE "insert into t1 values('Maths')","E0"                       'ignore error
  IF slGetChangeCount = 0 THEN INCR duplicates

  slEXE "insert into t1 values('Maths') on conflict(c1) do nothing" 'or upsert
  IF slGetChangeCount = 0 THEN INCR duplicates
NEXT

Viewer "select rowid,* from t1",duplicates

END FUNCTION

SUB Viewer(sql AS STRING,duplicates AS LONG)
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9"
? sql + $CR + JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Duplicates" + STR$(duplicates)
END SUB

[Fredrick Ughimi]

Hi CJ,

That worked! Thanks.

Code Select Expand


#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
LOCAL sSubject AS STRING
slOpen "junk.db3","C"
'slexe "drop table if exists t1"
slexe "create table if not exists t1(c1 unique)"
sSubject = "Chemistry"


slEXE "INSERT INTO t1(c1) VALUES('" + sSubject + "') ON CONFLICT(c1) DO NOTHING;"

IF slGetChangeCount = 0 THEN

    ? "Record Already Exist!", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$

    EXIT FUNCTION

ELSE

    ? "Record Saved!", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$

END IF

END FUNCTION
                               


Now the above code only Inserts a new record. Supposing I want to Update (Replace) a record?

[cj]

Now the above code only Inserts a new record. Supposing I want to Update (Replace) a record?
« Last Edit: Today at 03:02:38 AM by Fredrick

The purpose of Upsert is to update the other columns if the key is found without changing the key or rowid

This demo shows using Upsert where rowid stays the same if the key is found
vs
Replace Into where rowid will change if the key is found

There is nothing wrong using 2 steps since it is your program and this is an embedded database system.

I find nothing wrong with this:
1. Search by a  key/subject
2. If found UPDATE using rowid else INSERT

Note:
The key/subject can also be changed using Upsert so the rowid won't change.
But, if the new key (in the ON Conflict) exists an error will occur.
I don't think Upsert is meant to be used to change the key if it could cause a CONFLICT of the CONFLICT.
Example:
sql = "INSERT INTO t1 VALUES(" +_    'UPSERT
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"     +_
         "ON CONFLICT(c1_key) DO UPDATE SET c1_key = 'NewKey', c2_data = '" + sData + "'"

We can do anything in PowerBASIC after checking for an error using IF or CASE.
I like the Upsert statement, but will probably stick with searching for a key and acting upon the search result.
Others may HAVE to use all sql statements.  Upsert may not be the best solution.

In this demo program set %Upsert=0 to test REPLACE INTO instead of Upsert method.

Code Select Expand


'Method1. Upsert retains the rowid if an insert conflict and updates columns using ON CONFLICT
'Method2. Replace Into deletes existing row if found and inserts a new row

'Method3. Two step method may give you more options in my opinion (no example in this program)
'   Search for key/sSubject
'   If found THEN
'     UPDATE Table Set ... Where rowid=found
'   ELSE
'     Insert Into Table ...
'   End If
'
' -------------------------------------------------------------------
'   Set %Upsert=1 to test usng Upsert method
'   Set %Upsert=0 to test using Replace Into (which many do not like)
'--------------------------------------------------------------------

'  Notice rowid stays the same with Upsert if the key is found
'  Notice rowid changes if the key is found with Replace Into

#DIM ALL
#COMPILE EXE 'upsert2.bas
#INCLUDE "sqlitening.inc"
%Upsert = 0 '0=Use Replace Into which may do not like  '1=Use Upsert method to retain rowid
FUNCTION PBMAIN () AS LONG
LOCAL sql,sSubject,sData,sTitle AS STRING
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1_Key unique, c2_Data)"
sSubject = "Chemistry"
sTitle = IIF$(%Upsert,"UPSERT METHOD (rowid's stay the same)","REPLACE INTO (rowid's change)")
DO
  sSubject = INPUTBOX$("Please enter subject",sTitle,sSubject)
  IF LEN(sSubject) = 0 THEN EXIT DO
  sData    = "New time is " + TIME$
  IF %Upsert THEN
  sql = "INSERT INTO t1 VALUES(" +_    'UPSERT
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"     +_
         "ON CONFLICT(c1_key) DO UPDATE SET c2_data = '" + sData + "'"
  ELSE
  sql = "REPLACE INTO t1 VALUES(" +_   'REPLACE INTO
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"
  END IF

  slexe sql
  IF slGetChangeCount = 0 THEN ? "Serious Error"
  REDIM sArray(0) AS STRING
  slSelAry "select rowid,* from t1",sArray(),"Q9c"
  ?  sql + STRING$(2,$CR) + JOIN$(sArray(),$CR),%MB_SYSTEMMODAL, sTitle
LOOP
? "If you don't like Upsert or Replace Into, see method 3",,"Have a good day"
END FUNCTION

[Fredrick Ughimi]

Hello CJ,

Thank you for throwing more light.

The purpose of Upsert is to update the other columns if the key is found without changing the key or rowid

Yeah I found that out earlier. I am a bit 'disappointed'. Not what I earlier thought it was.

I like the Upsert statement, but will probably stick with searching for a key and acting upon the search result.

I need to go the search path as well.

Best regards.

[Bern Ertl]

Thanks cj.  I missed the single quotes.  That's what I get for replying when I was in a hurry.