it is necessary that the tables have the columns in the following order: t1, t2, t6, t3, t4, t5. Is it possible to create such a query so that it moves the given column to the place we need, without recreating the table again?

SQLite TABLE:

+----+----+----+----+----+----+ | t1 | t2 | t3 | t4 | t5 | t6 | +----+----+----+----+----+----+ | 1 | 2 | 3 | 4 | 5 | 6 | | 11 | 22 | 33 | 44 | 55 | 66 | |111 | 222| 333| 444| 555| 666| |1111|2222|3333|4444|5555|6666| +----+----+----+----+----+----+
  • Why do you need it? Select the columns in the correct order and everything - ilyaplot
  • I need the table to look like it was originally intended. I need to insert a column t6 between t2 and t3 (the table is already full) - Kill Noise
  • Explain clearly what you want to do? change the order of the columns in the table? Isn't it easier to form the result by specifying the columns in the required order? if you really want to change the physical order, then see the answer @cheops - Bald

1 answer 1

Unfortunately, SQLite does not support the full ALTER TABLE statement, which would allow you to change the order of the columns. Therefore, for the implementation of this task will have to perform a whole sequence of operations. One of the possible ways is as follows. Create a table alongside with the structure you need.

 CREATE TABLE new_table ( t1 INTEGER, t2 INTEGER, t6 INTEGER, t3 INTEGER, t4 INTEGER, t5 INTEGER );

Overtake data from the original table into it.

 INSERT INTO new_table SELECT t1, t2, t6, t3, t4, t5 FROM t;

Delete old table

 DROP TABLE t;

Rename the new table by assigning the name of the old table to it.

 ALTER TABLE new_table RENAME TO t;