SQLITE - switch data in columns

Question

Hi,

I've got a table with the following structure:

CREATE TABLE "mytable" ("column01" INTEGER NOT NULL , "column02" INTEGER NOT NULL )

And I want to switch the values between columns - I want column02 to become column01 and column01 to become column02.

i.e.:

column01 / column02
apple / 01
day / 05
light / 28

And I want it to become:

column01 / column02
01 / apple
05 / day
28 / light

Is there a way to achieve this, using only SQL query?

Thanks.

Answer 1

I can't try it here, but this might work. It's a swap that needs no temporary variables.

The algorithm is:

x -= y;
y += x;       // y gets the original value of x
x  = (y - x); // x gets the original value of y

so that would make your UPDATE statement like this:

UPDATE mytable
    SET column01 = column01 - column02,
        column02 = column02 + column01,
        column01 = column02 - column01

It will only work if the columns are evaluated in left-to-right order, and they are evaluated in place, as opposed to from a snapshot of the buffer, which I believe is the case for SQLite.

Answer 2

SQLITE is extremely limited in the ALTER table commands allowed. As stated on the official project site:

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a colum, remove a column, or add or remove constraints from a table.

Because of this, and because the two columns you want to swap are seemingly of different types (INTEGER and VARCHAR) I think you will need to export the table contents as sql/csv, drop the table, create a table with the structure you want, and then import the file you dropped back into that table.

One possible way to do this:

sqlite> .output my_outfile.sql

This changes output from displaying on screen to being written to a file.

sqlite> .dump my_table

This method will dump the CREATE TABLE SQL and all the INSERT statements as a transaction. You'll need to modify my_outfile.sql with vi or another editor to manually remove the CREATE TABLE statements, and I think you'll need to also remove the BEGIN, END transaction commands as I've had trouble importing data with them.

sqlite> .output stdout

This brings your "vision" back as command output will show on your screen again.

sqlite> CREATE TABLE. . .

Re-create the table with the column order you want, being sure to change the types (INTEGER/VARCHAR) appropriately.

sqlite> .read my_outfile.sql

This will execute all the SQL commands in the file you dumped earlier, which should result in achieving the goal you were after as the INSERT statements dumped do not associate column names with specific values.

So, that should do it. A bit verbose, but it may be the only way with sqlite.

Answer 3

I just tested the below query and it works:

update mytable set column01 = column02, column02 = column01