SQL statement for multiple row UPDATE in nodejs using node-pg library

Question

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 · Accepted Answer · 2022-01-12 10:06:53Z

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

edited Jan 12, 2022 at 10:06

answered Jan 12, 2022 at 9:57

Ayudh

1,7651 gold badge32 silver badges65 bronze badges

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

1 Comment

Ayudh Over a year ago

note this wouldn't return all the updated rows. the RETURNING * at the end would only return the last modified row.

Collectives™ on Stack Overflow

SQL statement for multiple row UPDATE in nodejs using node-pg library

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related