Are updates with joins possible in Drizzle?

[markacola]

I'm sorry if this has been answered elsewhere, but I couldn't find anything in the docs or discussions.

I am looking to build a query that runs an update with multiple joins, e.g.:

update "Task" t
  set "workspaceId" = ${targetWorkspaceId}
from "Workspace" w
  join "WorkspaceUser" wu on w."id" = wu."workspaceId"
where
  t."id" = ${taskId}
  and wu."userId" = ${userId}

I have landed on the following using sql for now:

await db.execute(sql`
  update "Task" t
    set "workspaceId" = ${targetWorkspaceId}
  from "Workspace" w
    join "WorkspaceUser" wu on w."id" = wu."workspaceId"
  where
    t."id" = ${id}
    and wu."userId" = ${userId}
`);

I was hoping to do something like the following but join is not available on PgUpdate<PgTableWithColumns<...>>:

await db
  .update(task)
  .set({
    workspaceId: targetWorkspaceId,
  })
  .from(task)
  .leftJoin(workspace, eq(workspace.id, task.workspaceId))
  .leftJoin(workspaceUser, eq(workspace.id, workspaceUser.workspaceId))
  .where(
    and(
      eq(task.id, id),
      eq(workspaceUser.userId, userId)
    )
  .execute();

Am I missing something obvious, or is this update-with-join not currently possible without raw SQL in Drizzle?

If it isn't currently possible and it's something that is planned/desired, then I would be happy to contribe!

[chrisjh]

I've encountered the same thing – doesn't seem that from or any join is possible at the moment.

Property 'from' does not exist on type 'PgUpdate<PgTableWithColumns<...>>
Property 'leftJoin' does not exist on type 'PgUpdate<PgTableWithColumns<...>>

[DenisBessa]

I think you can do it with a subquery.

const sq = await select({ id: table.id}) // your subquery here

await db.update(tasks).set(...).where(inArray(..., sq.id)

[LeonAlvarez]

that's not a subquery. It's 2 querys

[LeonAlvarez]

Bump this, trying to use update from in Postgresql

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

[ryanxcharles]

Also bump! I had to do a raw sql query for update joins and just got bit by a type error. It would be great if drizzle natively supported update joins. I'm happy to take a look at implementing this myself if the maintainers believe it would be something do-able by an outsider. (To me, naively, it seems like just a matter of being able to add some "join" text after the "update" text in the query - but I am not at all familiar with the drizzle code).

Here's a snippet of my code where I have to use raw sql, and where "update join" would be very helpful:

      await trx.execute(sql`
        UPDATE ${TableTransaction} AS t
        JOIN ${TableMerkleLeaf} AS ml ON t.id = ml.transaction_id
        SET t.state = 'block', t.block_num = ${header.blockNum.n}
        WHERE ml.domain = ${domain}
          AND ml.block_num = ${header.blockNum.n}
          AND ml.order_num <= ${orderNum}
      `);

[kravetsone]

Also bump! I had to do a raw sql query for update joins and just got bit by a type error. It would be great if drizzle natively supported update joins. I'm happy to take a look at implementing this myself if the maintainers believe it would be something do-able by an outsider. (To me, naively, it seems like just a matter of being able to add some "join" text after the "update" text in the query - but I am not at all familiar with the drizzle code).

Here's a snippet of my code where I have to use raw sql, and where "update join" would be very helpful:

      await trx.execute(sql`
        UPDATE ${TableTransaction} AS t
        JOIN ${TableMerkleLeaf} AS ml ON t.id = ml.transaction_id
        SET t.state = 'block', t.block_num = ${header.blockNum.n}
        WHERE ml.domain = ${domain}
          AND ml.block_num = ${header.blockNum.n}
          AND ml.order_num <= ${orderNum}
      `);

I see PR with support this

[hunterwilhelm]

I too would love to see this turned into a feature request.

[zsiegel92]

This is normal SQL and I expected it to be possible with Drizzle! I don't think there's a way to do this without a fully-string raw sql query. Of course one can use inArray or something as mentioned above but that shouldn't be necessary.