[SOLVED] Moving MYSQL field from one table to another

[Chappers]

Hi, just a quickie... is there a MySQL command to move a field from one table to another table? Or would I have to select data from a row in the table, turn into variables and then insert them into the other table?

Thanks in advance,
James

[printf]

Just use ALTER... add the new column to the table you want it added to. Then UPDATE using a sub-query to fill each row of the column you are moving, then DROP the the old column from the old table.

Example

Legend...

Old Database = old
Old Table = old_test
Old Column To Move = to_date
New Database = new
New Table = new_test
New Column To Make = new_date

1. create the new column in the [b]new[/b] database table [b]new_test[/b]

[code]ALTER TABLE new.new_test ADD new.new_test.new_date VARCHAR( 14 ) NOT NULL;[/code]

2. add the data from the [b]old[/b] database table [b]old_test[/b] to the [b]new[/b] database table [b]new_test[/b]

[code]UPDATE new.new_test SET new.new_test.new_date = (SELECT to_date FROM old.old_test WHERE old.old_test.name = new.new_test.name);[/code]

3. drop the old column from the [b]old[/b] database table [b]old_test[/b]

[code]ALTER TABLE old.old_test DROP to_date;[/code]


printf

[Chappers]

Sorry to be a pain, but can I just explain better what I want to do, just to see if what you recommend is still the way to do it?

Firstly, have I got the layout of MySQL tables right in my mind, so it'd be like this, for example:

|       id        |       name        |        age        |        email       |
--------------------------------------------------------------
|       1         |      David         |        22         | [email protected] |
|       2         |      Peter         |        45         | [email protected] |
|       3         |      Steve        |        31         | [email protected]  |

So fields make up the columns and data entered into the fields makes up the rows? So when I want complete information, I'm taking it from a row, in this case consisting of the information entered into 4 different columns?

Anyway, here's what I've got: I have one database containing two tables. One is called [b]inbox[/b] and the other is called [b]junk[/b]. Both contain the same fields: [b]username[/b], [b]subject[/b], [b]message[/b].

When necessary, I want to take the data for a user (username, subject, message) from table [b]inbox[/b] and then move that data into the other table [b]junk[/b]. In other words, someone will be able to move one of their messages consisting of fields username, subject, message for a particular ID from the inbox table to the junk table.

Thanks for your help.

[psychohagis]

why dont you add another column and call it something like [b]folder[/b]. then set the default as [u]inbox[/u]. then when they move it to junk you simply change [u]inbox[/u] to [u]junk[/u] and then in the select statement add a where clause i.e. [b]WHERE folder=inbox[/b]

This is how I would do it and i personanly think that would be easier, simpler, and take less processing.

[Chappers]

I've done as you suggest with the entry field FOLDER and it works perfectly. Thank you very much for your time, effort and knowledge.  :)