Batch Update multiple rows.

Is there a way to do batch update for multiple queries using update ... from syntax ? for example :

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

how to do this type of query for update. I know there is another way by using case, but its not performant as this for large updates.

3 Replies

Mario564•4mo ago

Hello. This syntax is currently not supported in Drizzle. You'll have to build the query with the sql operator

DoomD_007OP•4mo ago

mm, what if i use onConflictDoUpdate for batch updates.. how much would there be performance difference with native update... just a curious question..

Mario564•3mo ago

onConflictDoUpdate is for inserting rows, fairly sure it's not made for batch updates More specifically, onConflictDoUpdate is for updating a record that already exists instead of inserting the record due to a unique constraint or primary key collision

Gaming

Programming

Batch Update multiple rows.