deucalion0 deucalion0 - 2 months ago 7
SQL Question

What is the best way to query a table and insert all rows back into itself with two columns switched?

I have the following columns and an example row:

ID COL1 COL2 COL3 COL4

1 dat1 dat2 dat3 dat4


I want to perform a query that will leave me with this:

ID COL1 COL2 COL3 COL4

1 dat1 dat2 dat3 dat4

2 dat2 dat1 dat3 dat4


I was experimenting but so far not been able to achieve this, my last attempt:

INSERT INTO TABLE1(COL1, COL2)
SELECT COL2, COL1 DAT3 DAT4 FROM TABLE1


Any advice or tips are much appreciated.

Answer

I think you should use all the column in insert and you can get the value for selecct

INSERT INTO TABLE1 (COL1, COL2, COL3, COL4)
SELECT COL2, COL1, COL3, COL4  FROM TABLE1;

for adding new values you can

INSERT INTO TABLE1 (COL1, COL2, COL3, COL4)
SELECT COL2, COL1, 'new_value_for_col3', 'new_value_for_col4' FROM TABLE1;