Prisma Client: Update record field with value from related table

Question

I am trying to update a MySQL record using the Prisma Client (v.3.10.0), setting a field value using a lookup from a related table. Following is the raw query I would use, which works fine, of course:

UPDATE users
SET client_contract_id = (
  SELECT cc.id
  FROM client_contract cc
  JOIN client c on c.id = cc.client_id
    WHERE c.salesforce_id = '[...]' AND
          CURRENT_DATE BETWEEN cc.start_date AND cc.end_date
    )
WHERE auth0_user_id = 'xxxxx';

There related schemas are:

model client_contract {
  id                     Int       @id @default(autoincrement())
  client_id              Int?      @db.UnsignedInt
  salesforce_account_id  String    @db.VarChar(48)
  salesforce_contract_id String?   @db.VarChar(48)
  start_date             DateTime? @db.Date
  end_date               DateTime? @db.Date
  entitlements           Int?      @default(0) @db.TinyInt
  creation_date          DateTime? @default(now()) @db.Timestamp(0)
  created_by             Bytes?    @db.Binary(16)
  client                 client?   @relation(fields: [client_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "client_contract_client_id_fk")
  users                  users[]

  @@unique([salesforce_account_id, salesforce_contract_id], map: "client_contract_sf_account_id_sf_contract_id_uindex")
  @@index([client_id], map: "client_contract_client_id_fk")
}

model client {
  id                      Int                   @id @default(autoincrement()) @db.UnsignedInt
  name                    String                @db.VarChar(255)
  abbreviation            String?               @db.VarChar(255)
  state                   String?               @db.VarChar(255)
  banner_image            String?               @db.VarChar(255)
  address                 String?               @db.VarChar(255)
  city                    String?               @db.VarChar(255)
  zip                     String?               @db.VarChar(255)
  website                 String?               @db.VarChar(255)
  corner_image            String?               @db.VarChar(255)
  type                    Int                   @default(0) @db.UnsignedTinyInt
  parent_client_id        Int?                  @db.UnsignedInt
  is_international        Int                   @default(0) @db.UnsignedTinyInt
  creation_date           DateTime              @default(now()) @db.Timestamp(0)
  created_by_user_id      Bytes?                @db.Binary(16)
  category                String?               @db.VarChar(255)
  system_name             String                @db.VarChar(255)
  fiscal_year_start_month Int?                  @db.TinyInt
  salesforce_id           String?               @unique(map: "client_salesforce_id_uindex") @db.VarChar(24)
  annual_revenue_millions Int?                  @db.UnsignedInt
  industry                String?               @db.VarChar(128)
  sub_industry            String?               @db.VarChar(128)
  client_contract         client_contract[]
  client_email_domain     client_email_domain[]
  contract                contract[]
  delta_tag               delta_tag[]
  users                   users[]

  @@index([name], map: "name_2")
}

How would I construct a similar call with the Prisma Client?