In Drizzle, how can you insert into a MySQL Table from another table and update if exists?

Question

I want to use Drizzle to insert into a table, but if the key exists, then update the table.

In MySQL, I can write the following:

INSERT INTO myTable1(field1,field2,field3,field4)
SELECT fieldOne,fieldTwo,fieldThree,fieldFour
FROM myTable2
  ON DUPLICATE KEY UPDATE 
    field3 = VALUES(field3),
    field4 = VALUES(field4);

I would think I can use the following in Drizzle-ORM:

const insertUpdateTable = async (
    db: DBType,
    sourceTable: number, 
    targetTable: number,
) => {
    const sourceRecords = db.select({
        fieldOne: myTable2.fieldOne,
        fieldTwo: myTable2.fieldTwo,
        fieldThree: myTable2.fieldThree,
        fieldFour: myTable2.fieldFour
    })
    .from(myTable2);

    return await db.insert(myTable1)
        .values(sourceRecords)
        .onDuplicateKeyUpdate({
           set: {
                 field1: sourceRecords.fieldOne,
                 field2: sourceRecords.fieldTwo
           }
        })
}

So far, WebStorm indicates that my values(sourceRecords) and the onDuplicateKeyUpdate fields are not valid. I'm trying to preserve type safety by sticking to Drizzle and not using the Magic SQL operator. Thoughts?

Answer 1

Eventually, the solution presented itself.

return await db.insert(myTable1)
    .values(sourceRecords)
    .onDuplicateKeyUpdate({
       set: {
             field1: sql`VALUES(field1)`,
             field2: sql`field2`
       }
    })

For field 1, I'm accepting the incoming VALUES where as field2, I'm preserving the initial value in the destination table.

There's a level of risk as the magic sql doesn't enforce type-safety the way I want.