Move several columns from one mySQL table to another using phpMyAdmin

[irock]

I saw there's a text box in phpMyAdmin in which I can use native mySQL commands to do stuff. I was wondering what the codes are for moving several columns from one mySQL table to antoher lite mySQL table. I would like to do this using the 'Run SQL query/queries' text box in phpMyAdmin.

For example...
I want to move (productid, category, spec1, spec2, spec3, spec4) from TABLE1 to (productid, category, spec1, spec2, spec3, spec4) in TABLE2.

I appreicate any help that could help me on this.

[Birdman]

If you select "Operations" from the top menu, you can copy your table to a new table.

Birdman

[irock]

What if I only want to move only a few columns to the new table? What can I do?

I want to move (productid, category, spec1, spec2, spec3, spec4) from TABLE1 to (productid, category, spec1, spec2, spec3, spec4) in TABLE2.

TABLE1 has up to spec40, and TABLE2 only has up to spec10.

[hakre]

hi irock,

i think birdmans proposal is best for this. after copying the table, just open the preferences of the new one and drop the fields/colums you do not need.

there is another solution by creating a (temporary) table, but you'll need the latest (?!) max version of mysql and you have to type the sql string in by yourself. so this is more compilicated. if you need the sql strings for such an operation, please post.

[irock]

Sorry for the confusion.

I actually want to move (productid, category, spec1, spec2, spec3, spec4) from TABLE1 to (productid, category, spec9(w), spec(x), spec(y), spec4(z) in TABLE2.

In other words, the columns I'm going to move are different each time.

When I select a cateogry (9), (spec1, spec2, spec3, spec4) will be moved to (spec11, spec12, spec13, spec14). Therefore, I would like to know how to do this via mySQL statements.

Thanks again for your help!

[hakre]

hi irock,

just a question to follow up: does table2 already exists and you want extent it?

[irock]

TABLE2 already exists. There's a fixed number of columns in TABLE2, but there are already a large number of columns in TABLE1. The reason for the reorganization is to redistribute the spec(x) of TABLE1 into TABLE2.

For category 9,
category - 9
spec1 -> spec1
spec2 -> spec2
spec3 -> spec3
spec4 -> spec4

For category 10,
category - 10
spec5 -> spec1
spec6 -> spec2
spec7 -> spec3
spec8 -> spec4

I want to do something similar to this.

I only need a few general commands that can do this.

[hakre]

ok irock,

now i got the point. if you're familiar with sql (in general), then the hint is the

INSERT .... SELECT

sql command. this is for mysql i think you're using it and the command is described in detail with usercomments in the mysql docs [mysql.com].

in short it's a combination of an insert and a select command giving you all the possiblities you need: With

INSERT ... SELECT

statement you can quickly insert many rows into a table from one or many tables.

did this help?

[irock]

Thanks hakre, I think I'm proficient in PHP/mySQL language, but that only applies when I'm using mySQL on top of PHP. I'm not sure how executing a SELECT statement without assigning the values into an array can work with the next INSERT statement.

[andreasfriedrich]

The SELECT [mysql.com]ed rows never leave the MySQL [mysql.com] server. Instead they are INSERT [mysql.com]ed into the table given in the INSERT [mysql.com] part of the INSERT ... SELECT [mysql.com] statement.

There is nothing that prevents you from running such a query by using PHP [php.net]īs mysql_query() [php.net].

Andreas

[Birdman]

irock, I think the best solution for you is to write a small PHP [php.net] script, rather that trying to do it at the MySQL [mysql.com] command line.

You will need to store your selected data temporarily in a mysql_fetch_array() [php.net] then run a while loop and INSERT [mysql.com] them INTO your new table.

$sql = "SELECT [mysql.com] productid, category, spec1, spec2, spec3, spec4 FROM table1";
$result = mysql_query [php.net]($sql);
while($i = mysql_fetch_array [php.net]($result)){
$sql = "INSERT [mysql.com] INTO table2 VALUES($1[productid],'$i[category]','$i[spec1],$i[spec2],'$i[spec3]','$i[spec4]');
mysql_query [php.net]($sql);
}

Something like that ;)

[hakre]

hi irock,

since you didn't answer i don't know if my last suggestion worked out for your or you understood it.
i know that you want to put it into the phpmyadmin sql query field, this is the code which should work. i haven't tested it myself because i don't have this kind of db structure but since you posted some details, it might do a good job:

INSERT INTO table2 (spec1,spec2,spec3,spec4) SELECT 
spec1 AS spec1, spec2 AS spec2, spec3 AS spec3, spec4 AS spec4 
FROM table1 WHERE category=9;
INSERT INTO table2 (spec1,spec2,spec3,spec4) SELECT 
spec5 AS spec1, spec6 AS spec2, spec7 AS spec3, spec8 AS spec4 
FROM table1 WHERE category=10;

the phpmyadmin sql textarea should handle the linebreaks i put in to have a better reading here in this thread.