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

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

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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related