SQLite UPDATE with ORDER BY and LIMIT clauses

[john-black-3k]

I am trying to execute the following query on the chinook sample database:

UPDATE employees
SET Email = LOWER(
	FirstName || "." || LastName || "@chinookcorp.com"
)
ORDER BY
	FirstName
LIMIT 1;

However, I get the following error:

Error while executing SQL query on database 'chinook': near "ORDER": syntax error

According to the docs, ORDER BY and LIMIT clauses for UPDATE are available if SQLite has been built with SQLITE_ENABLE_UPDATE_DELETE_LIMIT.

I then checked the output of PRAGMA compile_options; and it contains ENABLE_UPDATE_DELETE_LIMIT so I guess that ORDER BY and LIMIT clauses for UPDATE should be available.

Moreover, running the same query through the sqlite3 command line shell results in an error also.

Please let me know what I am doing wrong.

Thank you!

[pawelsalawa]

Apparently SQLite used in SQLiteStudio must be built in a different way to make this compilation option work, according to https://sqlite.org/compile.html#enable_update_delete_limit

Currently the build flow uses SQLite amalgamation file, not the raw source code. I'll change it for the next release.

[pawelsalawa]

I've created #5190 to fix this.

[john-black-3k]

Hello Pawel,

Another minor issue with the UPDATE statement is that SQLiteStudio does not support a table alias in the UPDATE clause.

I tried the following query from this page:

UPDATE sales_employees AS e
SET
  salary = CASE s.score
    WHEN 1 THEN salary * 1.02 -- 2% increase for score 1
    WHEN 2 THEN salary * 1.04 -- 4% increase for score 2
    WHEN 3 THEN salary * 1.06 -- 6% increase for score 3
    WHEN 4 THEN salary * 1.08 -- 8% increase for score 4
    WHEN 5 THEN salary * 1.10 -- 10% increase for score 5
  END
FROM
  sales_performances AS s
WHERE
  e.id = s.sales_employee_id;

However, the parser underlines in red everything after "AS e" above. The alias in FROM clause does not cause any issues though. Executing the query works as expected, without errors, despite it being underlined in red.

Would it also be possible for the code assistant to show a dropdown with the columns in the actual table after typing an alias followed by a dot? For example, typing "s." above would show the columns from the sales_performances table, namely sales_employee_id and score.

Thank you!

[pawelsalawa]

SQLiteStudio's parser needs update on its internal SQL parser. Once it's done, it will not mark this as error and will suggest proper columns too. I'll do it as part of the issue created above.