Geek Answers Handbook

UPDATE multiple lines from multiple parameters in nodejs / pg

I am using NodeJS and pg with a PostgreSQL database as a backend. Running single queries with Client.query works great.

However, I have a problem that I can solve in a simple and elegant way.

I would like to run:

UPDATE portfolios SET votes = $1 WHERE id = $2

from array / list:

 [{votes: 5, id: 1}, {votes: 15, id: 1}, {votes: 25, id: 2}]

Is it possible to do this in one Client.query (or similar with pg), so I do not need to do "for () {...}"?

0
source share
1 answer

The driver should submit the request to Postgresql in this final form:

 update portfolios p set votes = s.votes from unnest(array[(5, 1), (15, 1), (25, 2)]) s (votes int, id int) where p.id = s.id

So pass this request to the driver:

 update portfolios p set votes = s.votes from unnest($1) s (votes int, id int) where p.id = s.id

with the corresponding array as a parameter.

Please note: if you build the string yourself, instead of letting the driver do this, you will be vulnerable to SQL injection.

+2
source

More articles:


All Articles