jhilliar Posts - Answer Overflow

jhilliar

•Created by jhilliar on 3/14/2024 in #help

Multi-row Updates

Anyone know a good way to do multi-row updates (when you only have a subset of the cols for the row). I’ve looked through the help and found 1) https://www.answeroverflow.com/m/1099708311030464583 and 2) https://github.com/drizzle-team/drizzle-orm/issues/1728. Seems like the main consensus is the insert/conflict option, but this won’t work for me since I only have a subset of the cols for the row (typing will throw an error since all cols are not present). I found this SQL statement on stack overflow (https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql) which seems to do what I want, but I’m not sure how to implement in drizzle.

UPDATE click
SET 
  click.revenue = revenue
FROM (
  values
    ('click-id', 1.2),
    ... A LOT MORE REV DATA,
    ('click-id-n', 3.2),
) as revForClick(clickId, revenue) 
where click.clickId = revClick.clickId;

UPDATE click
SET 
  click.revenue = revenue
FROM (
  values
    ('click-id', 1.2),
    ... A LOT MORE REV DATA,
    ('click-id-n', 3.2),
) as revForClick(clickId, revenue) 
where click.clickId = revClick.clickId;

1 replies

Gaming

Programming