Prisma•4mo ago

updateMany fails with "You can't specify target table 'Org' for update in FROM clause" error

For the following schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Org {
  id              Int    @id @default(autoincrement())
  name            String
  childOrgs       Org[]  @relation("Relation_Org_ParentChild")
  parentOrgs      Org[]  @relation("Relation_Org_ParentChild")
  childOrgsCount  Int    @default(0)
  parentOrgsCount Int    @default(0)
}

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Org {
  id              Int    @id @default(autoincrement())
  name            String
  childOrgs       Org[]  @relation("Relation_Org_ParentChild")
  parentOrgs      Org[]  @relation("Relation_Org_ParentChild")
  childOrgsCount  Int    @default(0)
  parentOrgsCount Int    @default(0)
}

running the following query results in an error

await prisma.org.updateMany({
  where: {
    childOrgs: {
      some: {
        id: 1,
      }
    },
  },
  data: {
    childOrgsCount: {
      increment: 1,
    },
  },
})

await prisma.org.updateMany({
  where: {
    childOrgs: {
      some: {
        id: 1,
      }
    },
  },
  data: {
    childOrgsCount: {
      increment: 1,
    },
  },
})

Error:

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(MysqlError { code: 1093, message: "You can't specify target table 'Org' for update in FROM clause", state: "HY000" })), transient: false })

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(MysqlError { code: 1093, message: "You can't specify target table 'Org' for update in FROM clause", state: "HY000" })), transient: false })

4 Replies

Lord Zd•4mo ago

I believe this is caused because you don't have the field and reference parameters in your relation. This part of the docs explains how to use self relations https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/self-relations

Self-relations | Prisma Documentation

How to define and work with self-relations in Prisma.

YoungHappyGuy•4mo ago

@Lord Zd Thank you for your reply. The docs mentions about my kind of schema in the last section and even the schema provided in the docs doesn't work (for updateMany) https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/self-relations#defining-multiple-self-relations-on-the-same-model

Self-relations | Prisma Documentation

How to define and work with self-relations in Prisma.

Lord Zd•3mo ago

Hey dude I had a similar problem with self relations I solved it by moving the relations to a separate table and splitting the query in to sections. This isn't a very elegant solution from my experience apologies I couldnt be of more help

YoungHappyGuy•3mo ago

@Lord Zd Thanks for the help

Gaming

Programming

updateMany fails with "You can't specify target table 'Org' for update in FROM clause" error