SQLite syntax error at UPDATE - works in SQLiteStudio, not in my code

Question

my problem is, that I wrote an update, checked it in SQLiteStudio, it works fine. When I execute the same in my program, it throws a syntax error. ('near "FROM": syntax error')

There is a difference in the sqlite3.dlls, because SQLiteStudio uses a 64bit one, my code is a 32bit Windows application, so it uses a 32bit dll. I found already some little differences (e.g. parenthesis is accepted or not) earlier in the function of the two versions, but there was always a solution to avoid the problem. (Unfortunately I don't know any exact version number, but the 32bit version is digitally signed by Idera Inc. in 20. February 2021., it is included by Delphi 10.4 Community version.)

But this time I have no idea, what can be the problem.

The original form of my UPDATE is:

UPDATE wRoutes SET tfFeedPt = fitt.tid FROM
(SELECT wr.id AS wid, tf.id AS tid FROM wRoutes wr 
JOIN xyposFitt tf 
ON wr.posX = tf.posX AND wr.posY = tf.posY
   AND wr.drNum = tf.drNum AND wr.page = tf.page
WHERE endpoint = "X") AS fitt
WHERE wRoutes.id = fitt.wid

It's perferct in SQLiteStudio, but throws the mentioned exception from my code.

I searched in Google a lot, and found a tip which seemed to be promising - to use the WITH UPDATE form, so I tried this version too:

WITH fitt AS
(SELECT wr.id AS wid, tf.id AS tid FROM wRoutes wr
JOIN xyposFitt tf  
ON wr.posX = tf.posX AND wr.posY = tf.posY  
AND wr.drNum = tf.drNum AND wr.page = tf.page 
 WHERE endpoint = "X")
UPDATE wRoutes SET tfFeedPt = fitt.tid 
FROM fitt
WHERE wRoutes.id = fitt.wid

It worked again in SQLiteStudio, but not in my code. I tried the table name aliases with and without using "AS". E.g. "FROM wRoutes wr" and "FROM wRoutes AS wr". The result is the same: SQLiteStudio OK, my code: 'near "FROM": syntax error'.

Could somebody tell, what is wrong with my UPDATE command?

Thank you in advance.

For the UPDATE...FROM syntax you need SQLite 3.33.0+. Check your version. — forpas
– forpas, Commented Jan 28, 2022 at 21:50
Thank you! This was the solution. I queried the version of the original dll from the code. It was 3.8.10.2. SQLiteStudio uses 3.35.4. I replaced the dll under Delphi with the latest, downloaded from the SQLite website (it is 3.37.2), and now it works. I didn't upgrade it until now, because there was a problem, which didn't happen when I executed my program from the IDE, but it appeared when I ran it on another machine, where the latest dll was deployed (as far as I remember, it was the parenthesis problem), so the original "Delphi version" seemed to be better. Thanks again! — Gabor
– Gabor, Commented Jan 28, 2022 at 22:45

Gabor · Accepted Answer · 2022-01-29 15:09:52Z

1

Forpas' comment contains the correct answer: the UPDATE...FROM syntax needs SQLite version 3.33.0+. Upgrade of the dll to the latest (3.37.2) solved the problem.

answered Jan 29, 2022 at 15:09

Gabor

112 bronze badges

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

Collectives™ on Stack Overflow

SQLite syntax error at UPDATE - works in SQLiteStudio, not in my code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related