I have a SqLite DB in which i have 16 columns as id, and f1 to f15.

Now Mistakenly I entered data from 2 column, leaving the first one empty. Now my all values are one place ahead i.e value of last column of first row is in 1st column of 2nd row.

Now I wish to correct this so that all values at their right place . Any query for this problem?


Assuming your ID column is correct and sequential, you can join the table to itself on the ID column = ID column + 1.


SELECT T1.id, T1.f2 AS f1, T1.f3 AS f2, etc, T2.f1 AS f15
FROM YourTable AS T1 
ON T1.id = T2.id + 1

Then with that dataset you can put it in a CTE (https://sqlite.org/lang_with.html) and then do an update statement on your table. I.e. you can update YourTable with the values from the CTE, e.g. SET YourTable.f15 = CTE.f15


UPDATE YourTable 
SET f1 = (SELECT f1
              FROM YourCte
              WHERE YourTable.id = YourCte.id)
                  FROM YourCte
                  WHERE YourTable.id = YourCte.id)  

Just fill in the rest of the columns in the SELECT of the CTE, and the UPDATE statement.

Your last record in the table will need to be manually fixed assuming it's last value is just missing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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