How to update multiple rows in node postgresql with prepared statement

user_9961247 0
Viewed 603

Database create

CREATE TABLE user(
    id serial primary key,
    point INT
)

INSERT INTO user(point)
VALUES (11),(22),(33);

Update database data in node

const { Pool, Client } = require('pg');
const pgPool = new Pool({...});
const query = "
    UPDATE user AS u 
    SET point = u2.point
    FROM(
        VALUES ($1, $2) ($3, $4)
    ) AS u2( id, point)
    WHERE u2.id = u.id;
"
const values = [1, 10, 2, 20]
pg.query(query, values)

Error

(node:9584) UnhandledPromiseRejectionWarning: error: operator does not exist: text = integer

If changing $1 $2 $3 $4 to 1 20 2 20, the query worked correctly, but I want to use a prepared statement for it, please help.

node.js prepared-statement sql-update postgresql
1 Answers

I checked your prepared statement code and found nothing wrong, other than the multiline string for the update, which looks wrong. You may try using either the backticks version:

const query = `
UPDATE user AS u 
SET point = u2.point
FROM (
    VALUES ($1, $2) ($3, $4)
) AS u2( id, point)
WHERE u2.id = u.id`;

Or for Node versions earlier than 4+ (ES6) you may use:

const query = "\
UPDATE user AS u \
SET point = u2.point \
FROM ( \
    VALUES ($1, $2) ($3, $4) \
) AS u2( id, point) \
WHERE u2.id = u.id";
user_18632290
Related