Kysely•4mo ago

How to delete and insert in one query

Hey! I wonder how with kysely make for example delete and insert in one query to reduce db roundtrips.

db.deleteFrom('a')
    .where('smthId', '=', a.id)
    .execute()
db.insertInto('b')
    .values(dto)
    .execute()

db.deleteFrom('a')
    .where('smthId', '=', a.id)
    .execute()
db.insertInto('b')
    .values(dto)
    .execute()

I believe Using WITH Clause (Common Table Expressions - CTEs) might help, but how kysely syntax would looks like? Should I simply use raw sql here?

BEGIN;

-- Perform the DELETE operation
DELETE FROM your_table
WHERE condition;

-- Perform the INSERT operation
INSERT INTO your_table (column1, column2)
VALUES (value1, value2);

COMMIT;

BEGIN;

-- Perform the DELETE operation
DELETE FROM your_table
WHERE condition;

-- Perform the INSERT operation
INSERT INTO your_table (column1, column2)
VALUES (value1, value2);

COMMIT;

Solution:

https://kyse.link/5L6l0

Jump to solution

3 Replies

Solution

koskimas•4mo ago

https://kyse.link/5L6l0

koskimas•4mo ago

https://kysely.dev/docs/category/cte

tzezarOP•4mo ago

as always thank you very much

Gaming

Programming

How to delete and insert in one query