postgres - how to do batch update with array in where clause

Question

So,I was hoping to do this:

 let statement = "update players set walk_count = unnest($1), x = unnest($2), y = unnest($3)  where player_id = unnest($4)";

But the error I get is "message: "set-returning functions are not allowed in WHERE",

The only other way I can solve this is by doing individual updates, but I see the loop is taking a lot of time.

I found transactions, which allows you to open a transaction to send bulk operations to the database, and defers execution until you call commit. That definitely helped. But I read that using unnest would have been faster. — endermaanny monooo
– endermaanny monooo, Commented Oct 18, 2022 at 2:56

user330315 · Accepted Answer · 2022-10-18 06:40:22Z

1

Assuming that each parameter ($1, $2, ...) is an array containing one item for each row you want to update, you should use a single unnest() call for all 4 arrays:

update players 
  set walk_count = v.wc, 
      x = v.x, 
      y = v.y
from (
  select *
  from unnest($1, $2, $3, $4)
) as v (wc, x, y, id)
where v.id = players.player_id

answered Oct 18, 2022 at 6:40

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres - how to do batch update with array in where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related