Hello I want to update different columns of multiple rows ha Prisma #orm-help

Hello, I want to update different columns of multi...

Arfath Tade

08/19/2021, 7:32 AM

Hello, I want to update different columns of multiple rows having different id's for example I have a model

Copy code

model Activities {
  id      String      @id @default(uuid())
  type    String
  minutes String
}

with data as

Copy code

id   |   type    | minutes
---------------------------
1    | running   | 72
2    | walking   | 92
3    | cycling   | 32

I want to update minutes of id 1 and 2 in one query. This is just a sample there can be multiple columns in the above model. Is there a way to achieve this bulk update through one query?

Ryan

08/19/2021, 7:51 AM

@Arfath Tade 👋 Currently you can only use a raw query for this. It would be great if you could create a feature request so that we can look into this 🙂

Arfath Tade

08/19/2021, 7:55 AM

Thanks Ryan! for instant response, I will have a look at the raw method. Highly appreciate your help!🙂

🙌 1

Arfath Tade

08/22/2021, 5:30 PM

Hey @Ryan I tried to $queryRaw but I am getting syntax error.

Copy code

await prisma.$queryRaw`UPDATE "Activities" SET minutes = c.minutes FROM (VALUES ${values} ) AS c(id, minutes) WHERE c.id = "Activities".id;`

with below error

Copy code

Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near "$1"`
    at cb (/home/node_modules/@prisma/client/runtime/index.js:34800:17)
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
   {
  code: 'P2010',
  clientVersion: '2.27.0',
  meta: {
    code: '42601',
    message: 'db error: ERROR: syntax error at or near "$1"'
  }
}

the same syntax is running in below example. can you please help me to understand what went wrong? http://sqlfiddle.com/#!17/198a3/654 Thanks!

Ryan

08/23/2021, 5:50 AM

Can you try it with

join

as shown here? Imported like this:

Copy code

import { Prisma } from '@prisma/client'

Prisma.join

Arfath Tade

08/23/2021, 10:46 AM

Thanks @Ryan We were able to accomplish this using the below query

Copy code

await prisma.$queryRaw(`UPDATE "Activities" AS ACT SET "minutes" = c.minutes FROM (VALUES ${values} ) AS c(id, minutes) WHERE c.id = ACT.id;`);

👍 1

5 Views

Open in Slack

Previous Next