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: Using The New UPSERT  (Read 728 times)

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Using The New UPSERT
« on: August 16, 2018, 09:25:08 AM »

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]
#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.


Logged
Fredrick O. Ughimi

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

Bern Ertl

  • Master Geek
  • ****
  • Posts: 496
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Using The New UPSERT
« Reply #1 on: August 16, 2018, 04:30:54 PM »

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

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Using The New UPSERT
« Reply #2 on: August 16, 2018, 05:01:56 PM »

Hello Bern,

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

Now I get a new error:

Logged
Fredrick O. Ughimi

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

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Using The New UPSERT
« Reply #3 on: August 16, 2018, 07:36:18 PM »

>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

« Last Edit: August 16, 2018, 08:15:03 PM by cj »
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Using The New UPSERT
« Reply #4 on: August 17, 2018, 02:11:22 AM »

Hi CJ,

That worked! Thanks.

Code: [Select]
#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?
« Last Edit: August 17, 2018, 03:02:38 AM by Fredrick Ughimi »
Logged
Fredrick O. Ughimi

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

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Using The New UPSERT vs REPLACE INTO
« Reply #5 on: August 17, 2018, 11:12:23 AM »

Quote
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]
'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
« Last Edit: August 17, 2018, 12:03:12 PM by cj »
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 601
    • View Profile
    • Software Development Company
Re: Using The New UPSERT
« Reply #6 on: August 17, 2018, 05:40:38 PM »

Hello CJ,

Thank you for throwing more light.

Quote
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.

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

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

Bern Ertl

  • Master Geek
  • ****
  • Posts: 496
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Using The New UPSERT
« Reply #7 on: August 20, 2018, 12:11:01 PM »

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