Drizzle Team•10mo ago

How to delete multiple rows whose value is contained in a list of values, at once?

If I want to delete multiple rows whose value (e.g. 'key') is included in a list of values, in raw SQL I can do something like this:

DELETE FROM example_table WHERE example_table.key IN (key1, key2, ...)

DELETE FROM example_table WHERE example_table.key IN (key1, key2, ...)

But, that seems to not be working correctly when using it in Drizzle, or I'm doing something completely wrong. Using MySQL. Here's my code:

const keysToDelete = ['key-1', 'key-2'];
await db
    .delete(exampleTable)
    .where(sql`${exampleTable.key} IN (${keysToDelete})`);

const keysToDelete = ['key-1', 'key-2'];
await db
    .delete(exampleTable)
    .where(sql`${exampleTable.key} IN (${keysToDelete})`);

I tried also joining the keys to delete as a single string using keysToDelete.join(",") but that also didn't work. The entries are not being removed from the database. However if I want to check against a single value within the IN (...) clause, it works fine.

1 Reply

pecko0326OP•10mo ago

EDIT: Nevermind, found the reason for the issue. Turns out that I didn't have to wrap the list of keys that I want to check against in parenthesis. So this:

await db
    .delete(exampleTable)
    .where(sql`${exampleTable.key} IN (${keysToDelete})`);

await db
    .delete(exampleTable)
    .where(sql`${exampleTable.key} IN (${keysToDelete})`);

should become this:

await db
    .delete(exampleTable)
    .where(sql`${exampleTable.key} IN ${keysToDelete}`);

await db
    .delete(exampleTable)
    .where(sql`${exampleTable.key} IN ${keysToDelete}`);

Lost way more time than I intended to on this 🙂

Gaming

Programming

How to delete multiple rows whose value is contained in a list of values, at once?