DT
Drizzle Team•2w ago
lewisedc

Is there any way to have a subquery in an insert statement?

I want to use drizzle to replicate this sql: 
INSERT INTO games (name, sortOrder)
VALUES ('New Game', COALESCE((SELECT MAX(sortOrder) FROM games), 0) + 1);
INSERT INTO games (name, sortOrder)
VALUES ('New Game', COALESCE((SELECT MAX(sortOrder) FROM games), 0) + 1);
However I don't know if there's a way to have a subquery in an insert statement like above. I've tried: 
await db.insert(games).values({
  name: 'New Game',
  sortOrder: db.select({ sortOrder: sql<number>`coalesce(${max(gamesTable.sortOrder)}, 0) + 1`
});
await db.insert(games).values({
  name: 'New Game',
  sortOrder: db.select({ sortOrder: sql<number>`coalesce(${max(gamesTable.sortOrder)}, 0) + 1`
});
But this doesn't work, can anyone shed some insight please?
3 Replies
Mario564
Mario564•2w ago
Try this: 
{ sortOrder: sql`(${
  db.select({ sortOrder: sql<number>`coalesce(${max(gamesTable.sortOrder)}, 0) + 1` })
})` }
{ sortOrder: sql`(${
  db.select({ sortOrder: sql<number>`coalesce(${max(gamesTable.sortOrder)}, 0) + 1` })
})` }
lewisedc
lewisedcOP•2w ago
I don't really understand why, but that seems to work, thank you very much!
Mario564
Mario564•2w ago
Subqueries aren't supported as insert values; however, wrapping it with the sql operator makes it of type SQL which is valid

Did you find this page helpful?