0

My question is the same as UPDATE multiple rows from multiple params in nodejs/pg however I face the issue the question author pointed out in the comments.

Basically, I want to perform a multi-row update and have such an array of objects like

const arr = [
  { image_id: 1, image_url: "xyz.com/image" }
]

Now I want to use that array to generate the SQL statement but just like the author in the link above, I don't know how to make an array of objects into an array of tuples like SQL expects.

Ayudh
  • 1,673
  • 1
  • 22
  • 55

1 Answers1

0

The following worked but I had to change my data structure to:

{
    "images": ["xyz.com/image"],
    "ids": [2]
}

UPDATE collection_images as ci 
SET image_url = new.image_url 
FROM (SELECT UNNEST($1::VARCHAR[]) as image_url, UNNEST($2::INT[]) as image_id) as new
WHERE ci.id = new.image_id
RETURNING *;

But I'm still wondering whether there's a way without changing the orignal data structure

Ayudh
  • 1,673
  • 1
  • 22
  • 55
  • note this wouldn't return all the updated rows. the `RETURNING *` at the end would only return the last modified row. – Ayudh Jan 15 '22 at 02:59