2

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.

6
  • What SQLite version are you using because update from syntax is supported from v3.33 version ? Sep 5, 2023 at 9:28
  • 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. Sep 5, 2023 at 9:50
  • Can I run it from bash somehow? Sep 5, 2023 at 10:10
  • 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 Sep 5, 2023 at 10:11
  • I have SQLite version 3.27.2 2019-02-25 16:06:06 Sep 5, 2023 at 10:16

1 Answer 1

2

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

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.