sqlite3 how to update table after reordering?-CodePudding

In python sqlite3, I want to UPDATE a table after reordering all the columns by a date_column for future use.

I can only find only 'SELECT', but I need to UPDATE and SAVE the table for future use.

cursor.execute(f"SELECT * FROM '{table_name}' ORDER BY date(date_column) DESC")

CodePudding user response：

To update existing data in a table, you use SQLite UPDATE statement.

---First, specify the table where you want to update after the UPDATE clause.

---Second, set new value for each column of the table in the SET clause.

---Third, specify rows to update using a condition in the WHERE clause. The WHERE clause is optional. If you skip it, the UPDATE statement will update data in all rows of the table.

---Finally, use the ORDER BY and LIMIT clauses in the UPDATE statement to specify the number of rows to update. Notice that if use a negative value in the LIMIT clause, SQLite assumes that there are no limit and updates all rows that meet the condition in the preceding WHERE clause.

The ORDER BY clause should always goes with the LIMIT clause to specify exactly which rows to be updated. Otherwise, you will never know which row will be actually updated; because without the ORDER BY clause, the order of rows in the table is unspecified.

CodePudding user response：

In short, UPDATE doesn't do what you think it does. It modifies the data, but doesn't re-order it. See the sqlite documentation for UPDATE, and Tim Biegeleisen's comment on the nature of databases.

If you need to speed up data retrieval, you could use an extra INDEX. Those can help make SELECT queries faster, but they tend to make INSERT queries slower.

But before you do that, profile your application and check that the sqlite queries are what actually takes the most time. If they do not, then don't bother optimizing them! If the queries do take the most time, use profiling to ensure that adding an INDEX actually improves the situation in your use case.