Postgres UPDATE inserted value instead of all columns

Question

imagine we have 10 columns in a table, and we want to update our table columns with input just 1 value and doesn't change remain values.

So just our new value will update and unnecessary values will not be Null ...?

i ASK this because i'm using This code and i want to send put requests depends on my edited content ...

i'm using this query to put request from body(react) into node postgres ...

router.put("/user/:nationalcode", (req, res) => {
  const cols = [
    req.body.nationalcode,
    req.body.stockcode,
    req.body.firstname,
    req.body.lastname,
    req.body.isenable,
    req.body.isonline,
    req.body.detail,
    req.body.birthdate,
    req.body.archive,
    req.body.offlineusername
  ];

  db.query(
    `UPDATE users SET nationalcode=$1, stockcode=$2, firstname=$3, lastname=$4, isenable=$5, isonline=$6, detail=$7, birthdate=$8, archive=$9, offlineusername = $10 WHERE nationalcode = ${req.params.nationalcode}`,
    cols,
    function(err, result) {
      if (err) {
        console.log("Error. Updating : %s ", err);
      }
    }
  );
  console.log(req.body);
});

Just use a normal UPDATE query. Like UPDATE table SET column = value WHERE x = y — iJamesPHP2, Feb 22 '20 at 02:44
@iJamesPHP2 i Updated my question with code , can u please check and solve it ? — tafhim, Feb 22 '20 at 02:53

iJamesPHP2 · Answer 1 · 2020-02-22T03:04:37.090

0

You want to strip out null values from your req.body object, use this function to strip it before you build the cols array:

function strip(obj) {
    for (var prop in obj) {
        if (obj[prop] === null || obj[prop] === undefined) {
            delete obj[prop];
        }
    }
}

So, define the function and use this as the first line of your code:

var cols = Object.values(strip(req.body));

edited Feb 22 '20 at 03:04

answered Feb 22 '20 at 02:59

iJamesPHP2

524
4
13

There was an error in my code, I didn't see you are using req.body and not just req to access the columns, please use my latest edit – iJamesPHP2 Feb 22 '20 at 03:05
@tafhim Oh, I've just realised your query is hardcoded, you'd then need to build the query dynamically from the array in a loop – iJamesPHP2 Feb 22 '20 at 03:10
what is the query? =) – tafhim Feb 22 '20 at 03:10
confused ... sry xD – tafhim Feb 22 '20 at 03:11
can we just make two variables like : ```column : requested col```` and ```value : req.body ``` ? this simple? – tafhim Feb 22 '20 at 03:14
https://stackoverflow.com/questions/30743475/how-to-use-dynamic-column-names-in-an-update-or-select-statement-in-a-function – tafhim Feb 22 '20 at 03:16

Postgres UPDATE inserted value instead of all columns

1 Answers1