Node-sqlite3: `UPDATE ... ORDER BY ... LIMIT ...` does not work

[elliotf]

According to sqlite documentation at http://www.sqlite.org/lang_update.html, I should be able to use order by and limit in an update, but node-sqlite3 does not let me, telling me that I have a syntax error near order.

Running the same query on the command line sqlite3 (version 3.7.17) results in one row being updated.

I'm sure I'm missing something, but I'm not sure what it is.

Example of problem below:

var sqlite3 = require('sqlite3');
var db = new sqlite3.Database(':memory:');

db.serialize(function() {
  db.run("CREATE TABLE waffles (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, number INTEGER)");
  db.run("INSERT INTO waffles (number) VALUES (5)");
  var stmt = db.prepare("UPDATE waffles SET number = number - ? WHERE number >= ? ORDER BY id ASC LIMIT 1");
  stmt.run(1,1);
  stmt.finalize();
});

db.close();                                                                                                 

Results in:

Error: SQLITE_ERROR: near "ORDER": syntax error 

[elliotf]

If anyone is waiting on this bug being fixed and is not aware of the fix below, you can do an update from a subselect in the meantime:

UPDATE waffles
SET number = number - ?
WHERE id IN (
  SELECT id
  FROM waffles
  WHERE number >= ?
  ORDER BY id ASC
  LIMIT 1
)

That will not help you if you're trying to use the simpler form of UPDATE ... LIMIT in an ORM, though.

[kkaefer]

The documentation you linked says:

If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses

It looks like you are running a version of SQLite that was compiled without that support.

[keverw]

I noticed "SQLITE_ENABLE_UPDATE_DELETE_LIMIT" is off too. It'd be nice if there was somthing we could put in our package.json maybe to enable it when doing a rebuild.

[Mithgol]

Actually, our compile-time options are not in package.json, they're in /deps/sqlite3.gyp.

[elliotf]

Based on @Mithgol 's tip, I took a naive stab at a pull request ( #699 )

But it looks like it's not working, which makes sense based on the following note in the documentation: If this option is defined, then it must also be defined when using the 'lemon' tool to generate a parse.c file. Because of this, this option may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website.

[Mithgol]

¯\_(ツ)_/¯

[claudiopro]

I'm hitting this too in https://github.com/emeraldion/adelia, would be nice to have control over limits and order in UPDATE and DELETE queries.

[kkaefer]

@claudiopro you can, but you'll have to compile everything, including sqlite3 itself, directly from source and set the correct compiler flags.