MySQL: Insert one column data to another

I'm not soo good at MySQL. But want to improve it a bit, to be able to manipulate tables without serverside languages.

I have one table and two columns. I need to select data from one column and insert in the same 开发者_如何学Ctable as rows instead.

As I understand I need to select this column data, save it in a temporary table or variable, then loop through results and insert them. But I do not know how to do it.

Could someone help with a simple example.

Thanks ;)

My table:

id | col1 | col2
-----------------
1  | txt  |  txt4
2  | txt2 |  txt5
3  | txt3 |  txt6

I need to merge col1 and col2.

Final result:

 id | col1
-----------
 1  |  txt
 2  |  txt2
 3  |  txt3
 4  |  txt4
 5  |  txt5
 6  |  txt6

This should work

insert into tablename
(col1_name)
select (col2_name) from tablename;

That will select all of your data from column 2 and insert it into column 1 as new rows. Then, you can drop the second column by doing:

alter table tablename drop col2_name;

Simply replace tablename with the name of your table, and the column names with the name of your column. Also, make sure that both column datatypes are compatible with each other.

Good luck!

If I am reading correct, you want to merge col1 and col2?? then, try this

alter table table1 add column col3 varchar(255);

update table1 set col3 = concat(col1, " ", col2);

alter table table1 drop column col1;
alter table table1 drop column col2;

You could use INSERT ... SELECT from the same table.

I tried this and it worked for me

update table1 set column2=column1;