K
Kysely•2mo ago
A(-a)

MERGE INTO using arbitrary values

Hi 👋🏽 I'm trying to construct a MERGE query using arbitrary values based on this stackoverflow answer. Here's the example from that post: 
MERGE INTO mytable AS tbl
USING (VALUES
    ('val1', 'val2'),
    ('val3', 'val4')
) AS vals (col1, col2)
ON tbl.col1 = vals.col1
WHEN matched THEN
  UPDATE SET col2 = vals.col2
WHEN NOT matched THEN
  INSERT (col1, col2)
  VALUES (vals.col1, vals.col2); 
MERGE INTO mytable AS tbl
USING (VALUES
    ('val1', 'val2'),
    ('val3', 'val4')
) AS vals (col1, col2)
ON tbl.col1 = vals.col1
WHEN matched THEN
  UPDATE SET col2 = vals.col2
WHEN NOT matched THEN
  INSERT (col1, col2)
  VALUES (vals.col1, vals.col2);
I'm struggling to figure out how to do the USING clause with kysely. I'm trying to do it with the values helper from the kysely docs, but .mergeInto doesn't seem to have an equivalent to insertInto's .expression method, so I can't figure out if that can be made to work. Thanks for the help!
Extending kysely | Kysely
In many cases, Kysely doesn't provide a built-in type-safe method for a feature. It's often because adding
Solution:
Thanks, that's almost perfect (you did make a mistake, the alias for values should have been 'vals'). For posterity's sake, here's the full query to get output SQL matching the example: ``` const rows = await db...
Jump to solution
2 Replies
koskimas
koskimas•2mo ago
Try this 
.using(
  values([{col1: 'val1', col2: 'val2'}], 'tbl'), 
  (u) => u.onRef('tbl.col1', '=', 'vals.col1'),
)
.using(
  values([{col1: 'val1', col2: 'val2'}], 'tbl'), 
  (u) => u.onRef('tbl.col1', '=', 'vals.col1'),
)
Solution
A(-a)
A(-a)•2mo ago
Thanks, that's almost perfect (you did make a mistake, the alias for values should have been 'vals'). For posterity's sake, here's the full query to get output SQL matching the example: 
const rows = await db
  .mergeInto('mytable as tbl')
  .using(
    values([{col1: 'val1', col2: 'val2'}, {col1: 'val3', col2: 'val4'}], 'vals'), 
    (u) => u.onRef('tbl.col1', '=', 'vals.col1'),
  )
  .whenMatched()
  .thenUpdateSet({
    col2: sql.ref('vals.col2')
  })
  .whenNotMatched()
  .thenInsertValues({
    col1: sql.ref('vals.col1'),
    col2: sql.ref('vals.col2')
  }).execute()
const rows = await db
  .mergeInto('mytable as tbl')
  .using(
    values([{col1: 'val1', col2: 'val2'}, {col1: 'val3', col2: 'val4'}], 'vals'), 
    (u) => u.onRef('tbl.col1', '=', 'vals.col1'),
  )
  .whenMatched()
  .thenUpdateSet({
    col2: sql.ref('vals.col2')
  })
  .whenNotMatched()
  .thenInsertValues({
    col1: sql.ref('vals.col1'),
    col2: sql.ref('vals.col2')
  }).execute()

Did you find this page helpful?