onDuplicateKeyUpdate

i'm having problems using onDuplicateKeyUpdate, doing it like

// values is an array of entries
await db.insert(myTable).values(values).onDuplicateKeyUpdate({  set: {values } })

// values is an array of entries
await db.insert(myTable).values(values).onDuplicateKeyUpdate({  set: {values } })

causes an error TypeError: Cannot read properties of undefined (reading 'name') and doing it with set: { id: myTable.id } causes an error TypeError: Converting circular structure to JSON my goal is to update the whole entry/row when there's a conflict in the primary key which based on the name onDuplicateKeyUpdate gets triggered with

44 Replies

--MON--•13mo ago

fkkkkkk 🤣 i found the way so i just did

await db.insert(myTable).values(values).onDuplicateKeyUpdate({  set: { id: sql`${myTable.id}` } })

await db.insert(myTable).values(values).onDuplicateKeyUpdate({  set: { id: sql`${myTable.id}` } })

and do the same as id: sql`${myTable.id} for the rest of the columns new problem doing this seems to update the value to the previous one making the update worthless, but it does help in not throwing an error when inserting several batches of data now let's say i have

const values = [
{id: 1, message: "hello", lastUpdated: new Date()},
{id: 2, message: "hi", lastUpdated: new Date()},
{id: 3, message: "haha", lastUpdated: new Date()},
]

const values = [
{id: 1, message: "hello", lastUpdated: new Date()},
{id: 2, message: "hi", lastUpdated: new Date()},
{id: 3, message: "haha", lastUpdated: new Date()},
]

using this one

await db.insert(myTable).values(values).onDuplicateKeyUpdate({  set: { id: sql`${myTable.id}`, message: sql`${myTable.message}`, lastUpdated: new Date() } })

await db.insert(myTable).values(values).onDuplicateKeyUpdate({  set: { id: sql`${myTable.id}`, message: sql`${myTable.message}`, lastUpdated: new Date() } })

now doing this will update my lastupdated but not the message

Angelelz•13mo ago

I believe you need to use target to let it know where is the conflict

--MON--•13mo ago

so how do i get the value being inserted to be used on the onDuplicateKeyUpdate set does that property exist? cause intellisense only shows set and no target or where property

Angelelz•13mo ago

It should: https://orm.drizzle.team/docs/insert#onconflict-and-upsert-insert-or-update

SQL Insert - DrizzleORM

Drizzle ORM | %s

--MON--•13mo ago

yeah that's the old method which does not exist in the version i am using which is version ^0.28.6 this one checks using the sql's ON DUPLICATE KEY UPDATE thing

mr_pablo•13mo ago

await db.insert(myTable)
.values({id: id, name: name, etc})
.onDuplicateKeyUpdate({
  set: {name: name, etc}
})

await db.insert(myTable)
.values({id: id, name: name, etc})
.onDuplicateKeyUpdate({
  set: {name: name, etc}
})

--MON--•13mo ago

so the only problem i now have is how do i get the current value being inserted and pass it on the set as i said, the value i am inserting is an ARRAY or Values

mr_pablo•13mo ago

well it can't be, it has to be an object the above code will work, its exactly what im doing in my code

--MON--•13mo ago

that's the problem i am facing now, how do i get the current value being inserted

Angelelz•13mo ago

So value is an array?

mr_pablo•13mo ago

take drizzle out of the equation; how would you get the current value ?

--MON--•13mo ago

the problem of doing this is, i would have to loop and it would cause several transactions instead of just 1

mr_pablo•13mo ago

this doesnt seem like a drizzle issue

--MON--•13mo ago

yup array of entries

Angelelz•13mo ago

I don't think that's possible

Gaming

Programming

onDuplicateKeyUpdate