DT
Drizzle Team•2mo ago
Valerka

Update many rows

Is it possible to update many rows in the table using db.update(table).set(...).from() ? Specifically I want to achive this sql query: 
UPDATE users u
SET
  score   = v.new_score,
  status  = v.new_status,
  rank    = v.new_rank
FROM (
  VALUES
    (1, 100, 'active',  5),
    (3,  85, 'inactive', 7),
    (5, 120, 'pending', 3)
) AS v(id, new_score, new_status, new_rank)
WHERE u.id = v.id
RETURNING *
UPDATE users u
SET
  score   = v.new_score,
  status  = v.new_status,
  rank    = v.new_rank
FROM (
  VALUES
    (1, 100, 'active',  5),
    (3,  85, 'inactive', 7),
    (5, 120, 'pending', 3)
) AS v(id, new_score, new_status, new_rank)
WHERE u.id = v.id
RETURNING *
But cannot get .from working.
3 Replies
JustWayne
JustWayne•2mo ago
I think you're going to have to use SQL for that one, with the sql tagged template thing
Valerka
ValerkaOP•2mo ago
Are you sure? It just feels like there should be some way to use drizzle methods, I mean there is .from method, but I only can get it working using another table and not from dynamic values
JustWayne
JustWayne•2mo ago
At the very least, I would think you'd need at least some SQL e.g. at least for .from(sql'VALUES (...)' perhaps something along the lines of this:
Subqueries can be used in any place where a table can be used, for example in joins: ...
https://orm.drizzle.team/docs/select#select-from-subquery If you're using TypeScript, then the only problem left will be casting your results or the parameters to your .select() call or whatever. You can see some of the type-casting I had to do for generic query building here - https://discord.com/channels/1043890932593987624/1415789562793558076

Did you find this page helpful?