SQLite3: how to change files type without data loss

Question

In my project, I use sqlite database, unfortunately my friend made a mistake. The field is not of the correct type. So, at the moment when the data in the "localid" field (declare as an integer) is more than 2147483647, all entries in this field are set to max 2147483647.

The sql query query alter table / alter does not work with sqlite because it supports a limited subset of ALTER TABLE: only rename and add a new column.

So how can I make changes without losing data? to create a new database correctly, copy all the data into it and delete the old one?

But maybe there is a better way? Does anyone have an idea?

Answer 1

Follow these steps:

• create a temporary table that contains the fields that form the primary key and localid (I assume this is not PK).
• fill in temporary table
• delete old column
• add new column
• fill in the new column by selecting from the temporary table (+ possible conversion to a new type).

Do not forget about possible foreign keys if the column is used as such, and remember the possibility of temporarily easing the constraint if it makes the transformation smooth (probably not necessary in your case).