Avinash Karat Avinash Karat - 1 year ago 66
SQL Question

Renaming two columns or swapping the values? Which one is better?

I have a table with more than 15 lakh records, in which I have two columns, say A and B. Mistakenly the column values of A got inserted into the column B and column B's values got inserted to A. Recently only we found the issue. So under these circumstances what will be the best option to correct this issue? Rename the column names interchangingly (I don't know how it can be possible, since if we nename A to B, when B already exists), or swapping the values contained in the two columns?

Any help will be deeply appreciated.

Answer Source

You can have the below query to swap the columns,

UPDATE table_name  SET  A = B, B = A; 

But you have huge amount of date in this case renaming will be good. But renaming of column name because of data issue is not a right solution. So you can have above update query to update your data.

Before updating take a backup of table which you are updating using the query,

CREATE TABLE table_name_bkp AS SELECT * FROM table_name;

Always have a backup while playing with original data which will not mess up

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download