How can I update multiple columns in sequelize.query

Question

For now I update my table accounts with accountId and it's working perfectly.

return models.sequelize.query("UPDATE \"accounts\" " +
                                        "SET \"accountId\" = "+newAccount.id+" " +
                                        "WHERE \"accountId\" = "+oldAccount.id+";").spread(function(){
            return 'success';
        });

What if I want to change not only accountId, but, say, the date. How should I write it then? I've tried writing it with the comma

"SET \"accountId\" = "+ newAccount.id+",\"date\" + newAccount.date + " WHERE...

but that doesn't seem to work.

Appreciate your help.

UPDATE: in console I get this message [2015-10-25 16:42:00.909] [TRACE] main - Executing (default): UPDATE "accounts" SET "date" = Sun Oct 25 2015 16:42:00 GMT+0300 (MSK) WHERE "date" = Sun Oct 25 2015 16:41:53 GMT+0300 (MSK); but after that I don't get any 'success' message (data didn't change in db). May it happen because of data type? I have 'timestamp with time zone' in my postgresql database.

I guess, here can be the same problem

I think you missed one = after date. "SET \"accountId\" = "+ newAccount.id+ ",\"date\" =" + newAccount.date + " WHERE... — Majid Yaghouti
– Majid Yaghouti, Commented Oct 25, 2015 at 12:31
@MajidYaghouti updated the code, but no changes. In fact now I have this string UPDATE "accounts" SET "accountId" = (new_value), "date" = (new_value) WHERE "accountId" = (value), "date" = (value);, but it doesn't work at all: even accountID doesn't update. — a-a
– a-a, Commented Oct 25, 2015 at 13:22
Remember to use binds, otherwise you may be vulnerable to SQLi attacks! e.g. query("UPDATE a SET b = :id", {replacements: {id: "123"}}); — Dan Dart
– Dan Dart, Commented Jul 10, 2020 at 14:56

Evan Siroky · Accepted Answer · 2015-10-25 23:14:50Z

2

When you try to query by a date, you are sending over a JavaScript date object which gets converted into a string of the local time. PostgreSQL then rejects this due to invalid syntax both because the date doesn't get quoted and because it won't recognize the format.

Whenever possible, try not use raw queries when using Sequelize, because Sequelize can do all of the necessary serialization and deserialization for you. Your some issue could easily be done by writing this:

var Account = sequelize.define('account', {
  accountId: Sequelize.INTEGER,
  date: Sequelize.DATE
});

Account.update({
  accountId: newAccount.id,
  date: new Date()
}, {
  where: {
    accountId: oldAccount.id
  }
}).then(function() {
  callback('success');
});

If you really want to do this with a raw query, you should convert your date object into something that PostgreSQL can read. You could do this with the moment library for instance:

var moment = require('moment');

models.sequelize.query("UPDATE \"accounts\" " +
 "SET \"accountId\" = " + newAccount.id + ", " +
   "\"date\" = '" + moment().format('YYYY-MM-DD HH:mm:ss') + "' " +
 "WHERE \"accountId\" = " + oldAccount.id + ";").spread(function(){
 return 'success';
});

answered Oct 25, 2015 at 23:14

Evan Siroky

9,4366 gold badges58 silver badges74 bronze badges

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

5 Comments

a-a Over a year ago

Thanks for help, but in postgres logs I found this error:

2015-10-26 19:51:54 MSK ERROR:  syntax error at or near "43" at character 55 2015-10-26 19:51:54 MSK STATEMENT:  UPDATE "accounts" SET "date" = 2015-10-26 23:40:45.363+03 WHERE "date" = 2015-10-26 19:40:45.363+03;

a-a Over a year ago

Assuming that in db 'date' column you will see something like this"2015-10-26 20:33:02.255+03"

Evan Siroky Over a year ago

You're forgetting to quote the date.

a-a Over a year ago

Thanks for help and patience!

Dan Dart Over a year ago

Remember to use binds, otherwise you may be vulnerable to SQLi attacks! e.g. query("UPDATE a SET b = :id", {replacements: {id: "123"}});

Collectives™ on Stack Overflow

How can I update multiple columns in sequelize.query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related