CODEWITHSUNDEEP
mysql
Strawberry
Strawberry

Reputation: 67888

MySQL: Re-doing my structures. How can I move my existing data?

I decided to redo my MySQL table structure. How can I move the data into the new tables from old tables. What would be the best way to do it? Currently, I am using phpmyadmin. Is there a way to pipe like in Unix i.e. SELECT * FROM table | UPDATE ?

Upvotes: 0

Views: 52

Answers (3)

ajreal
ajreal

Reputation: 47321

You don't have to, simply do something like

create table new_table like old_table;
insert into new_table select * from old_table;

If the structure is different:

create table new_table ...; <-- whatever definition

insert into new_table 
select 
  (FUNCTION_CALL to change column value)
from old_table;

example

insert into new_table 
select 
  concat(title, ' by ', author) as title ...
from old_table;

Upvotes: 0

user111013
user111013

Reputation:

INSERT...SELECT is your best bet. Reference: http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

eg: 

INSERT INTO destinationTable (col1, col2, col3) 
SELECT oldcol1, oldcol2, oldcol3 
FROM sourceTable

Upvotes: 2

The Scrum Meister
The Scrum Meister

Reputation: 30111

You can move data into the new tables by executing

INSERT newtable SELECT * FROM oldtable

if the new tables aren't created yet:

CREATE TABLE newtable
SELECT * FROM oldtable

Upvotes: 0

Related Questions