3 replies

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?

Drizzle Team•14mo ago•

3 replies

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?

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

Similar Threads

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

Similar Threads

Similar Threads

Similar Threads