Kysely•13mo ago

`update set from`

Accroding to @koskimas, kysely supports the update set from sql syntax. So far, I've been unable to figure out how (in lieu of dropping down to raw sql). We are planning to craft many of our update statements as batch operations in our repository layer, so this pattern will be used heavily. Here's some example sql to make it clear (hopefully) what I'm trying to do

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

Reference: https://github.com/kysely-org/kysely/issues/677#issuecomment-1707741394

GitHub

Is there a Kysely workaround for bulk updates in a single table? · ...

I'm searching for a Kysley query to perform a bulk update of my table data.

Solution:

And here's that helper for you https://kyse.link/?p=s&i=C0yoagEodj9vv4AxE3TH

Jump to solution

4 Replies

jpayneOP•13mo ago

I just stumbled on this example which is close to what I'm looking to do. Just can't wrangle the gnarly types into something that .set() likes https://kysely.dev/docs/recipes/extending-kysely#a-more-complex-example

Extending kysely | Kysely

In many cases Kysely doesn't provide a built-in type-safe method for a feature. It's often because adding

koskimas•13mo ago

https://kyse.link/?p=s&i=frTjKggpMcplveADROBN The part that needs raw SQL is the values statement, but you can replace that with selectNoFrom Here's your example query https://kyse.link/?p=s&i=L61rRViRBWGd7qbeBiLg The values part obviously needs to be extracted into a helper function and made dynamic using sql.join and stuff. The example is not readable.

Solution

koskimas•13mo ago

And here's that helper for you https://kyse.link/?p=s&i=C0yoagEodj9vv4AxE3TH

jpayneOP•13mo ago

This is awesome! Thanks @koskimas.

Gaming

Programming

`update set from`