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

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.

CodePudding user response：

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