/ Check-in [2c1b1987]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Back off of the extended upsert syntax that allows multiple ON CONFLICT clauses. The syntax now is exactly as in PostgreSQL and MySQL. Add support for WHERE clauses on the conflict-target phrase, for partial indexes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: 2c1b1987d8de1efa8ed7e1f199710e32ff20edf8ceec570514fc63bb1ef264e0
User & Date: drh 2018-04-13 13:06:45
Context
2018-04-13
13:44
Improved conflict-target matching logic. check-in: 98d32ba6 user: drh tags: upsert
13:06
Back off of the extended upsert syntax that allows multiple ON CONFLICT clauses. The syntax now is exactly as in PostgreSQL and MySQL. Add support for WHERE clauses on the conflict-target phrase, for partial indexes. check-in: 2c1b1987 user: drh tags: upsert
01:15
Begin adding upsert logic. This is an incremental check-in. check-in: 80969643 user: drh tags: upsert
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
    for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
      assert( pIdx );
      aRegIdx[i] = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert ){
    pTabList->a[0].iCursor = iDataCur;
    sqlite3UpsertAnalyze(pParse, pTabList, pUpsert);
  }
#endif


  /* This is the top of the main insertion loop */
  if( useTempTable ){
    /* This block codes the top of loop only.  The complete loop is the







|

|







801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
    for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
      assert( pIdx );
      aRegIdx[i] = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert && pUpsert->pUpsertTarget ){
    pTabList->a[0].iCursor = iDataCur;
    sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
  }
#endif


  /* This is the top of the main insertion loop */
  if( useTempTable ){
    /* This block codes the top of loop only.  The complete loop is the

Changes to src/parse.y.

866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
{
  sqlite3Insert(pParse, X, 0, F, R, 0);
}

%type upsert {Upsert*}
%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
upsert(A) ::= . { A = 0; }
upsert(A) ::= upsert(X) ON CONFLICT LP sortlist(Y) RP
              DO UPDATE SET setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,X,Y,Z,W); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON DUPLICATE KEY UPDATE setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,X,0,Z,W); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON CONFLICT LP sortlist(Y) RP DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,X,Y,0,0); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,X,0,0,0); /*X-overwrites-A*/ }

%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}

%type idlist_opt {IdList*}
%destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}







|

|
|
|
|
|
|
|







866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
{
  sqlite3Insert(pParse, X, 0, F, R, 0);
}

%type upsert {Upsert*}
%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
upsert(A) ::= . { A = 0; }
upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW)
              DO UPDATE SET setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W);}
upsert(A) ::= ON DUPLICATE KEY UPDATE setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,0,0,Z,W); }
upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,T,TW,0,0); }
upsert(A) ::= ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,0,0,0,0); }

%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}

%type idlist_opt {IdList*}
%destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}

Changes to src/sqliteInt.h.

2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719

2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733


2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
....
4283
4284
4285
4286
4287
4288
4289
4290
4291
4292
4293
4294
4295
4296
4297
4298
4299
4300
#define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
#define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
#define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
#define NC_Complex   0x2000  /* True if a function or subquery seen */

/*
** An instance of the following object describes a single ON CONFLICT
** clause in an upsert.  A list of these objects may be attached to
** an INSERT statement in order to form an upsert.
**
** The pUpsertTarget field is only set if the ON CONFLICT clause includes
** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the
** conflict-target clause.)

**
** pUpsertSet is the list of column=expr terms of the UPDATE statement. 
** The pUpsertSet field is NULL for a ON CONFLICT DO NOTHING.  The
** pUpsertWhere is the WHERE clause for the UPDATE and is NULL if the
** WHERE clause is omitted.
**
** The pUpsertIdx is a transient pointer to the unique index described
** by pUpsertTarget.  If pUpsertTarget describes the rowid, then pUpsertIdx
** will be NULL.  pUpsertIdx does not own the Index object it points to.
** Care must be taken to ensure that the Index object does not expire while
** the pointer is valid.
*/
struct Upsert {
  ExprList *pUpsertTarget;  /* Optional description of conflicting index */


  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */
  Index *pUpsertIdx;        /* UNIQUE index referenced by pUpsertTarget */
  Upsert *pUpsertNext;      /* Next ON CONFLICT clause in the list */
};

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
................................................................................
  void sqlite3WithDelete(sqlite3*,With*);
  void sqlite3WithPush(Parse*, With*, u8);
#else
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)
#endif
#ifndef SQLITE_OMIT_UPSERT
  Upsert *sqlite3UpsertNew(sqlite3*,Upsert*,ExprList*,ExprList*,Expr*);
  void sqlite3UpsertDelete(sqlite3*,Upsert*);
  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
  int sqlite3UpsertAnalyze(Parse*,SrcList*,Upsert*);
#else
#define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)     ((Upsert*)0)
#endif









|
<



|
>





<
<
<
<
<
<



>
>


<
<







 







|


|







2707
2708
2709
2710
2711
2712
2713
2714

2715
2716
2717
2718
2719
2720
2721
2722
2723
2724






2725
2726
2727
2728
2729
2730
2731


2732
2733
2734
2735
2736
2737
2738
....
4277
4278
4279
4280
4281
4282
4283
4284
4285
4286
4287
4288
4289
4290
4291
4292
4293
4294
#define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
#define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
#define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
#define NC_Complex   0x2000  /* True if a function or subquery seen */

/*
** An instance of the following object describes a single ON CONFLICT
** clause in an upsert.

**
** The pUpsertTarget field is only set if the ON CONFLICT clause includes
** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the
** conflict-target clause.)  The pUpsertTargetWhere is the optional
** WHERE clause used to identify partial unique indexes.
**
** pUpsertSet is the list of column=expr terms of the UPDATE statement. 
** The pUpsertSet field is NULL for a ON CONFLICT DO NOTHING.  The
** pUpsertWhere is the WHERE clause for the UPDATE and is NULL if the
** WHERE clause is omitted.






*/
struct Upsert {
  ExprList *pUpsertTarget;  /* Optional description of conflicting index */
  Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */
  Index *pUpsertIdx;        /* Constraint that pUpsertTarget identifies */
  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */


};

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
................................................................................
  void sqlite3WithDelete(sqlite3*,With*);
  void sqlite3WithPush(Parse*, With*, u8);
#else
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)
#endif
#ifndef SQLITE_OMIT_UPSERT
  Upsert *sqlite3UpsertNew(sqlite3*,ExprList*,Expr*,ExprList*,Expr*);
  void sqlite3UpsertDelete(sqlite3*,Upsert*);
  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
  int sqlite3UpsertAnalyzeTarget(Parse*,SrcList*,Upsert*);
#else
#define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)     ((Upsert*)0)
#endif


Changes to src/upsert.c.

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

40
41
42
43
44
45
46
47
48
49
50
51

52
53
54
55
56
57
58
59

60
61
62
63
64

65
66
67
68
69
70
71
72
73
74
75
76

77
78
79
80
81
82
83
84
85
86


87
88
89
90
91







92
93
94






95
96
97
98
99
100
101
102











103

104
105
106






107
108
109
110
111
112
113







114
115
116
117
118
119







120
121
122
123
124




125
126


127
128
129
130
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_UPSERT
/*
** Free a list of Upsert objects
*/
void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
  while( p ){
    Upsert *pNext = p->pUpsertNext;
    sqlite3ExprListDelete(db, p->pUpsertTarget);
    sqlite3ExprListDelete(db, p->pUpsertSet);
    sqlite3ExprDelete(db, p->pUpsertWhere);
    sqlite3DbFree(db, p);
    p = pNext;
  }
}

/*
** Duplicate an Upsert object.
*/
Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
  if( p==0 ) return 0;
  return sqlite3UpsertNew(db,
           sqlite3UpsertDup(db, p->pUpsertNext),
           sqlite3ExprListDup(db, p->pUpsertTarget, 0),

           sqlite3ExprListDup(db, p->pUpsertSet, 0),
           sqlite3ExprDup(db, p->pUpsertWhere, 0)
         );
}

/*
** Create a new Upsert object.
*/
Upsert *sqlite3UpsertNew(
  sqlite3 *db,           /* Determines which memory allocator to use */
  Upsert *pPrior,        /* Append this upsert to the end of the new one */
  ExprList *pTarget,     /* Target argument to ON CONFLICT, or NULL */

  ExprList *pSet,        /* UPDATE columns, or NULL for a DO NOTHING */
  Expr *pWhere           /* WHERE clause for the ON CONFLICT UPDATE */
){
  Upsert *pNew;
  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
  if( pNew==0 ){
    sqlite3UpsertDelete(db, pPrior);
    sqlite3ExprListDelete(db, pTarget);

    sqlite3ExprListDelete(db, pSet);
    sqlite3ExprDelete(db, pWhere);
    return 0;
  }else{
    pNew->pUpsertTarget = pTarget;

    pNew->pUpsertSet = pSet;
    pNew->pUpsertNext = pPrior;
    pNew->pUpsertWhere = pWhere;
  }
  return pNew;
}

/*
** Analyze the ON CONFLICT clause(s) described by pUpsert.  Resolve all
** symbols in the conflict-target clausees.  Fill in the pUpsertIdx pointers.
**
** Return non-zero if there are errors.

*/
int sqlite3UpsertAnalyze(
  Parse *pParse,     /* The parsing context */
  SrcList *pTabList, /* Table into which we are inserting */
  Upsert *pUpsert    /* The list of ON CONFLICT clauses */
){
  NameContext sNC;
  Upsert *p;
  Table *pTab;
  Index *pIdx;


  int rc = SQLITE_OK;
  int nDoNothing = 0;

  assert( pTabList->nSrc==1 );
  assert( pTabList->a[0].pTab!=0 );







  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;






  pTab = pTabList->a[0].pTab;
  for(p=pUpsert; p; p=p->pUpsertNext){
    if( p->pUpsertTarget==0 ){
      if( p->pUpsertSet ){
        /* This is a MySQL-style ON DUPLICATE KEY clause.  The ON DUPLICATE
        ** KEY clause can only be used if there is exactly one uniqueness
        ** constraint and/or PRIMARY KEY */
        int nUnique = 0;











        for(pIdx = pTab->pIndex; pIdx; pIdx=pIdx->pNext){

          if( IsUniqueIndex(pIdx) ){
            p->pUpsertIdx = pIdx;
            nUnique++;






          }
        }
        if( pTab->iPKey>=0 ) nUnique++;
        if( nUnique!=0 ){
          sqlite3ErrorMsg(pParse, "ON DUPLICATE KEY may only be used if there "
               "is exactly one UNIQUE or PRIMARY KEY constraint");
          return SQLITE_ERROR;







        }
      }else{
        nDoNothing++;
        if( nDoNothing>1 ){
          sqlite3ErrorMsg(pParse, "multiple unconstrained DO NOTHING clauses");
          return SQLITE_ERROR;







        }
      }
      continue;
    }
    rc = sqlite3ResolveExprListNames(&sNC, p->pUpsertTarget);




    if( rc ) return rc;
  }


  return rc;
}

#endif /* SQLITE_OMIT_UPSERT */







|
|
|



<









<

>










<

>






<

>





>

|
|





|
|

|
>

|


|


<


>
>
|
<



>
>
>
>
>
>
>



>
>
>
>
>
>

<
|
<
<
<
<
<
>
>
>
>
>
>
>
>
>
>
>
|
>
|
<
<
>
>
>
>
>
>
|
|
<
<
<
<
<
>
>
>
>
>
>
>


<
<
<
<
>
>
>
>
>
>
>
|
|
<
|
<
>
>
>
>
|

>
>
|



15
16
17
18
19
20
21
22
23
24
25
26
27

28
29
30
31
32
33
34
35
36

37
38
39
40
41
42
43
44
45
46
47
48

49
50
51
52
53
54
55
56

57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

85
86
87
88
89

90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

110





111
112
113
114
115
116
117
118
119
120
121
122
123
124


125
126
127
128
129
130
131
132





133
134
135
136
137
138
139
140
141




142
143
144
145
146
147
148
149
150

151

152
153
154
155
156
157
158
159
160
161
162
163
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_UPSERT
/*
** Free a list of Upsert objects
*/
void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
  if( p ){
    sqlite3ExprListDelete(db, p->pUpsertTarget);
    sqlite3ExprDelete(db, p->pUpsertTargetWhere);
    sqlite3ExprListDelete(db, p->pUpsertSet);
    sqlite3ExprDelete(db, p->pUpsertWhere);
    sqlite3DbFree(db, p);

  }
}

/*
** Duplicate an Upsert object.
*/
Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
  if( p==0 ) return 0;
  return sqlite3UpsertNew(db,

           sqlite3ExprListDup(db, p->pUpsertTarget, 0),
           sqlite3ExprDup(db, p->pUpsertTargetWhere, 0),
           sqlite3ExprListDup(db, p->pUpsertSet, 0),
           sqlite3ExprDup(db, p->pUpsertWhere, 0)
         );
}

/*
** Create a new Upsert object.
*/
Upsert *sqlite3UpsertNew(
  sqlite3 *db,           /* Determines which memory allocator to use */

  ExprList *pTarget,     /* Target argument to ON CONFLICT, or NULL */
  Expr *pTargetWhere,    /* Optional WHERE clause on the target */
  ExprList *pSet,        /* UPDATE columns, or NULL for a DO NOTHING */
  Expr *pWhere           /* WHERE clause for the ON CONFLICT UPDATE */
){
  Upsert *pNew;
  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
  if( pNew==0 ){

    sqlite3ExprListDelete(db, pTarget);
    sqlite3ExprDelete(db, pTargetWhere);
    sqlite3ExprListDelete(db, pSet);
    sqlite3ExprDelete(db, pWhere);
    return 0;
  }else{
    pNew->pUpsertTarget = pTarget;
    pNew->pUpsertTargetWhere = pTargetWhere;
    pNew->pUpsertSet = pSet;
    pNew->pUpsertWhere = pWhere;
    pNew->pUpsertIdx = 0;
  }
  return pNew;
}

/*
** Analyze the ON CONFLICT clause described by pUpsert.  Resolve all
** symbols in the conflict-target.
**
** Return SQLITE_OK if everything works, or an error code is something
** is wrong.
*/
int sqlite3UpsertAnalyzeTarget(
  Parse *pParse,     /* The parsing context */
  SrcList *pTabList, /* Table into which we are inserting */
  Upsert *pUpsert    /* The ON CONFLICT clauses */
){
  NameContext sNC;

  Table *pTab;
  Index *pIdx;
  ExprList *pTarget;
  Expr *pTerm;
  int rc;


  assert( pTabList->nSrc==1 );
  assert( pTabList->a[0].pTab!=0 );
  assert( pUpsert!=0 );
  assert( pUpsert->pUpsertTarget!=0 );

  /* Resolve all symbolic names in the conflict-target clause, which
  ** includes both the list of columns and the optional partial-index
  ** WHERE clause.
  */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
  if( rc ) return rc;
  rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
  if( rc ) return rc;

  /* Check to see if the conflict target matches the rowid. */  
  pTab = pTabList->a[0].pTab;

  pTarget = pUpsert->pUpsertTarget;





  if( HasRowid(pTab) 
   && pTarget->nExpr==1
   && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
   && (pTerm->iColumn==XN_ROWID || pTerm->iColumn==pTab->iPKey)
  ){
    /* The conflict-target is the rowid of the primary table */
    assert( pUpsert->pUpsertIdx==0 );
    return SQLITE_OK;
  }

  /* Check for matches against other indexes */
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int ii, jj, nn;
    if( !IsUniqueIndex(pIdx) ) continue;


    if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
    if( pIdx->pPartIdxWhere ){
      if( pUpsert->pUpsertTargetWhere==0 ) continue;
      if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
                             pIdx->pPartIdxWhere, pTabList->a[0].iCursor)!=0 ){
        continue;
      }
    }





    nn = pIdx->nKeyCol;
    for(ii=0; ii<nn; ii++){
      if( pIdx->aiColumn[ii]!=XN_EXPR ){
        for(jj=0; jj<nn; jj++){
          if( pTarget->a[jj].pExpr->op!=TK_COLUMN ) continue;
          if( pTarget->a[jj].pExpr->iColumn!=pIdx->aiColumn[ii] ) continue;
          break;
        }
      }else{




        Expr *pExpr;
        assert( pIdx->aColExpr!=0 );
        assert( pIdx->aColExpr->nExpr>ii );
        pExpr = pIdx->aColExpr->a[ii].pExpr;
        for(jj=0; jj<nn; jj++){
          if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr, -1)==0 ){
            break;
          }
        }

      }

      if( jj<nn ) break;
    }
    if( ii>=nn ) continue;
    pUpsert->pUpsertIdx = pIdx;
    return SQLITE_OK;
  }
  sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
                          "PRIMARY KEY or UNIQUE constraint");
  return SQLITE_ERROR;
}

#endif /* SQLITE_OMIT_UPSERT */

Changes to test/upsert1.test.

12
13
14
15
16
17
18
19

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

36


37
38
39
40
41
42
43
44
45










46
# Test cases for UPSERT

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix zipfile

do_execsql_test upsert1-100 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);

  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
  SELECT * FROM t1;
} {1 2}
do_execsql_test upsert1-101 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
  SELECT * FROM t1;
} {2 3}
do_execsql_test upsert1-102 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(3,4)
      ON CONFLICT(a) DO NOTHING
      ON CONFLICT(b) DO NOTHING;
  SELECT * FROM t1;
} {3 4}
do_catchsql_test upsert1-110 {

  DELETE FROM t1;


  INSERT INTO t1(a,b) VALUES(3,4)
      ON CONFLICT DO NOTHING
      ON CONFLICT DO NOTHING;
} {1 {multiple unconstrained DO NOTHING clauses}}
do_catchsql_test upsert1-120 {
  INSERT INTO t1(a,b) VALUES(3,4)
      ON DUPLICATE KEY UPDATE a=99;
} {1 {ON DUPLICATE KEY may only be used if there is exactly one UNIQUE or PRIMARY KEY constraint}}











finish_test







|
>


|




|


|
<
<

|

>
|
>
>
|
<
|
|
|
|
|
|

>
>
>
>
>
>
>
>
>
>

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


32
33
34
35
36
37
38
39

40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
# Test cases for UPSERT

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix zipfile

do_execsql_test upsert1-100 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
  CREATE UNIQUE INDEX t1x1 ON t1(b);
  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
  SELECT * FROM t1;
} {1 2 0}
do_execsql_test upsert1-101 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
  SELECT * FROM t1;
} {2 3 0}
do_execsql_test upsert1-102 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;


  SELECT * FROM t1;
} {3 4 0}
do_catchsql_test upsert1-110 {
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
  SELECT * FROM t1;
} {1 {no such column: x}}
do_catchsql_test upsert1-120 {
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;

  SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_catchsql_test upsert1-130 {
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
  SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}

if 0 {
do_catchsql_test upsert1-200 {
  DROP INDEX t1x1;
  DELETE FROM t1;
  CREATE UNIQUE INDEX t1x1 ON t1(a||b);
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||b) DO NOTHING;
  SELECT * FROM t1;
} {0 {5 6}}
}

finish_test