Question SQLite SQL help

I am very new to Sqlite and need help to recount the id row. This would be from 0 to end.

Id: 1, 2, 3, 8, 9
to
id: 1, 2, 3, 4, 5

I have the following sql code to do this:

Code (CSharp):

1. void RecountDB_id()
2.     {
3.         connection = "URI=file:" + Application.dataPath + "/Database/" + "FamilyQuiz.db";
4.         dbconn = (IDbConnection)new SqliteConnection(connection);                            //creates database connection
5.         dbconn.Open();
6.  
7.         dbcmd = dbconn.CreateCommand();
8.  
9.         sqlQuery = "SET (@id = 0);" +
10.             "UPDATE questions " +
11.             "SET id = (@id := @id + 1);";
12.  
13.          print(sqlQuery);
14.  
15.         int _sql = 0;
16.  
17.         try
18.         {
19.             dbcmd.CommandText = sqlQuery;
20.             _sql = dbcmd.ExecuteNonQuery();
21.         }
22.         catch (Exception e)
23.         {
24.             print("ERROR: " + e);
25.             print("sql: " + _sql);
26.         }
27.  
28.        
29.  
30.         CloseDB();
31.  
32.         print("DONE");
33.     }

I get the following error message:

Code (CSharp):

1. ERROR: Mono.Data.Sqlite.SqliteException (0x80004005): SQLite error
2. near "SET": syntax error

I have tested all kinds of SQL-UPDATEs but found this that suppose to work.

Anyone that can help me to do this?

 

This sounds more like a SQL syntax problem than a Unity problem.

I don't know what SQL syntax is acceptable for any given installation of SQL/SQLite but it seems you have something wrong with your expression.

Print out the expression before you execute it, and then see if it looks reasonable once it is all printed out as a single string.

 

It looks like you may be trying to update a primary key? Typically that is prohibited to maintain referential integrity. And you logic seems that would only update each ID to ID + 1 (1,2,3 becomes 2,3,4). You might want to craft your query first and test it https://sqlitebrowser.org/