Delete limit

Hi, how do I delete just 1 row from a table?

// Example:
db.delete(dogs).where(eq(dogs.breed, 'husky')).limit(1)

// Example:
db.delete(dogs).where(eq(dogs.breed, 'husky')).limit(1)

7 Replies

NinjaBunny•2y ago

you can pass in the id of the specific dog you’d like to delete so where dog is husky AND dogId is …. you have to be more specific if you want the query to only delete 1 row

makaron pelnoziarnistyOP•2y ago

yeah but Id need to make a query first right? No other way to do it with just one query rn?

NinjaBunny•2y ago

well it depends, how are you getting to the point of I want to delete 1 dog certainly that dog’s data will have everything necessary for us to delete it if we so choose

makaron pelnoziarnistyOP•2y ago

um, im not sure what you mean I just want to delete any dog of the given breed Can I do better than this?:

// I suppose it is more beneficial to wrap it up in a transaction function
await db.transaction(async (tx) => {
    const dog = await tx.query.dogs.findFirst({
        where: (dogs, { eq }) => eq(dogs.breed, 'husky')
    });

    await db.delete(dogs).where(eq(dogs.id, dog.id));
});

// I suppose it is more beneficial to wrap it up in a transaction function
await db.transaction(async (tx) => {
    const dog = await tx.query.dogs.findFirst({
        where: (dogs, { eq }) => eq(dogs.breed, 'husky')
    });

    await db.delete(dogs).where(eq(dogs.id, dog.id));
});

NinjaBunny•2y ago

if you want to delete just 1 dog without any thought into which dog gets deleted this is prob the best approach

DoggeSlapper•3mo ago

Use subqueries

Mario564•3mo ago

There's a PR that will implement limit in delete and update in MySQL and SQLite. This will be merged after this upcoming release

Gaming

Programming

Delete limit