SQLite

          }
        }
        seenReplace = 1;
        break;
      }
#ifndef SQLITE_OMIT_UPSERT
      case OE_Update: {
        sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur);
        /* Fall through */
      }
#endif
      case OE_Ignore: {
        sqlite3VdbeGoto(v, ignoreDest);
        break;
      }

      case OE_Abort:
      case OE_Fail: {
        sqlite3UniqueConstraint(pParse, onError, pIdx);
        break;
      }
#ifndef SQLITE_OMIT_UPSERT
      case OE_Update: {
        sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iIdxCur+ix);
        /* Fall through */
      }
#endif
      case OE_Ignore: {
        sqlite3VdbeGoto(v, ignoreDest);
        break;
      }

#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*);
  void sqlite3UpsertDoUpdate(Parse*,Upsert*,Table*,Index*,int);
#else
#define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)     ((Upsert*)0)
#endif

  sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
                          "PRIMARY KEY or UNIQUE constraint");
  return SQLITE_ERROR;
}

/*
** Generate bytecode that does an UPDATE as part of an upsert.
**
** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK.
** In this case parameter iCur is a cursor open on the table b-tree that
** currently points to the conflicting table row. Otherwise, if pIdx
** is not NULL, then pIdx is the constraint that failed and iCur is a
** cursor points to the conflicting row.
*/
void sqlite3UpsertDoUpdate(
  Parse *pParse,        /* The parsing and code-generating context */
  Upsert *pUpsert,      /* The ON CONFLICT clause for the upsert */
  Table *pTab,          /* The table being updated */
  Index *pIdx,          /* The UNIQUE constraint that failed */

  int iCur              /* Cursor for pIdx (or pTab if pIdx==NULL) */
){
  Vdbe *v = pParse->pVdbe;
  sqlite3 *db = pParse->db;
  int regKey;               /* Register(s) containing the key */
  Expr *pWhere;             /* Where clause for the UPDATE */
  Expr *pE1, *pE2;
  SrcList *pSrc;            /* FROM clause for the UPDATE */

  assert( v!=0 );
  VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
  pWhere = sqlite3ExprDup(db, pUpsert->pUpsertWhere, 0);
  if( pIdx==0 || HasRowid(pTab) ){
    /* We are dealing with an IPK */
    regKey = ++pParse->nMem;
    if( pIdx ){
      sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey);
    }else{
      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey);
    }
    pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
    if( pE1 ){
      pE1->pTab = pTab;
      pE1->iTable = pParse->nTab;
      pE1->iColumn = -1;
    }

    int iTab = pParse->nTab+1;
    Index *pX;
    for(pX=pTab->pIndex; ALWAYS(pX) && !IsPrimaryKeyIndex(pX); pX=pX->pNext){
      iTab++;
    }
    for(i=0; i<pIdx->nKeyCol; i++){
      regKey = ++pParse->nMem;
      sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regKey);
      j = pIdx->aiColumn[i];
      VdbeComment((v, "%s", pTab->aCol[j].zName));
      pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
      if( pE1 ){
        pE1->pTab = pTab;
        pE1->iTable = iTab;
        pE1->iColumn = j;

# 2018-04-17
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for UPSERT

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

foreach {tn sql} {
  1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE) }
  2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) }
  3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID}
} {
  reset_db
  execsql $sql

  do_execsql_test 1.$tn.0 {
    INSERT INTO t1 VALUES(1, NULL, 'one');
    INSERT INTO t1 VALUES(2, NULL, 'two');
    INSERT INTO t1 VALUES(3, NULL, 'three');
  }
  
  do_execsql_test 1.$tn.1 {
    INSERT INTO t1 VALUES(1, NULL, 'xyz') ON CONFLICT DO NOTHING;
    SELECT * FROM t1;
  } {
    1 {} one 2 {} two 3 {} three
  }
  
  do_execsql_test 1.$tn.2 {
    INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT DO NOTHING;
    SELECT * FROM t1;
  } {
    1 {} one 2 {} two 3 {} three
  }
  
  do_execsql_test 1.$tn.3 {
    INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT (c) DO UPDATE SET b = 1;
    SELECT * FROM t1;
  } {
    1 {} one 2 1 two 3 {} three
  }
  
  do_execsql_test 1.$tn.4 {
    INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET b=2;
    SELECT * FROM t1;
  } {1 {} one 2 2 two 3 {} three}

  do_catchsql_test 1.$tn.5 {
    INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) 
      DO UPDATE SET c = 'one';
  } {1 {UNIQUE constraint failed: t1.c}}

  do_execsql_test 1.$tn.6 {
    SELECT * FROM t1;
  } {1 {} one 2 2 two 3 {} three}
}

finish_test