How should I shift values to one place back in SqLite

sqlite

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?

Best Answer

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

Psuedo-Example:

SELECT T1.id, T1.f2 AS f1, T1.f3 AS f2, etc, T2.f1 AS f15
FROM YourTable AS T1 
INNER JOIN YourTable AS T2
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

Psuedo-Example:

UPDATE YourTable 
SET f1 = (SELECT f1
              FROM YourCte
              WHERE YourTable.id = YourCte.id)
 WHERE EXISTS (SELECT 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.

Related Question