How to Set Run Values for SQLite Upsert

[cliftonlabrum]

I'm trying to do the SQLite-equivalent of an "upsert" which includes an INSERT followed by an UPDATE. I'm unclear on how to set the values for the second set of ? placeholders.

const insert = db.prepare(`INSERT INTO achievement
  (id, name, kind)
  VALUES (?, ?, ?)
  ON CONFLICT(id) DO UPDATE SET 
  id = ?, name = ?, kind = ?`)
.run(1, 'Tom', 'Certificate')

Do I repeat the run() values again? Or will including them once apply to both sets of ? placeholders?

//Do I need to do this?
.run(1, 'Tom', 'Certificate', 1, 'Tom', 'Certificate')
//Or this?
.run(1, 'Tom', 'Certificate')

[Prinzhorn]

Did you try it? The first one gives

RangeError: Too few parameter values were provided

because you have six ? but only provided three values. Numbered placeholders are not supported yet (#725), so I recommend using named parameters.

const insert = db.prepare(`
  INSERT INTO achievement
  ( id,  name,  kind) VALUES
  (:id, :name, :kind)
  ON CONFLICT(id) DO UPDATE SET
  id = :id, name = :name, kind = :kind
`);

insert.run({ id: 1, name: 'Tom', kind: 'Certificate' });

They are also more robust if you later change the query, e.g. there is absolutely no reason you are updating id to the same value:

const insert = db.prepare(`
  INSERT INTO achievement
  ( id,  name,  kind) VALUES
  (:id, :name, :kind)
  ON CONFLICT(id) DO UPDATE SET
  name = :name, kind = :kind
`);

[cliftonlabrum]

Thank you!