DBMI-SQLite driver error: Error in sqlite3_prepare(): near ".": syntax error in db.execute command

Question

I try to make a query with inner join from grass gis (but in that community this question was concerned as offtopic, so it seems to concern only SQL lite):

db.execute sql="UPDATE A SET A.next2=B.next_stream 
 FROM stream_order2 AS A 
 INNER JOIN stream_order2 AS B ON A.factor=B.stream"

I have to do this because I would like to set values of next2 as values of next stream from table corresponding to stream numbers equal to factor value. db.execute throws an error.

Then I updated the command and removed A. statement. Now the error is:

Error in sqlite3_prepare():
 near "FROM": syntax error

I tried to remove A. after ON, but it doesn't help, I don't think it's the issue.

• Ergest Basha: What SQLite version are you using because update from syntax is supported from v3.33 version ?
• Nikolay Yasinskiy: I can't find exactly what version it is, but as in throws sqlite3 error, seem to be 3 and up. Grass I have is 7.8.7. version. Developers may write somewhere about sql version, but I can't find it.
• Nikolay Yasinskiy: Can I run it from bash somehow?
• Ergest Basha: If by bash you mean cli such as terminal ? If so yes and the first thing you should see would be something like SQLite version 3.28.0 2019年04月15日 14:49:49
• Nikolay Yasinskiy: I have SQLite version 3.27.2 2019年02月25日 16:06:06

Answer 1

I have SQLite version 3.27.2 2019年02月25日 16:06:06

UPDATE FROM is supported beginning in SQLite version 3.33.0 (2020年08月14日).

You can get around this by using a correlated subquery and having suitable indexes ,

UPDATE stream_order2
SET next2 = (SELECT B.next_stream
 FROM stream_order2 B
 WHERE B.stream=stream_order2.factor )
WHERE EXISTS ( SELECT 1
 FROM stream_order2 B
 WHERE B.stream=stream_order2.factor)

See example here

• Nikolay Yasinskiy: It works. Thanks.
• Nikolay Yasinskiy: Yes, sorry, I have forgotten. I am aware