Posted by Bob Stearns on 05/04/06 08:00

Rich wrote:
> On Tue, 02 May 2006 21:40:57 -0700, cover wrote...
>
>>I have a field that I seemed to have placed in the wrong table as I
>>tried to normalize my db. Is there a way to move a column (field) to
>>another table and place it at the end of the table within the same db?
>>Am using Apache, PHP, and MySQL TIA
>
>
> There's ALTER commands available in SQL that allow you to drop columns, add
> them, or modify them, but not aware of any that can move a column.
>
> Usually with an SQL database there is a relationship between the values in each
> column on a row. You'd typically do that with a primary key or secondary key,
> but usually have to build some association since the values in the new column
> need to match up properly with the existing columns in that table.
>
> You could probably get that moved over pretty painlessly though. You can create
> the new column in the destination table. Then write a small script to read the
> data from the current table (capture it into an array), then loop through the
> array to update the destination table and its new column.
>
> Rich
>
>
If the keys are the same/similar:

with table2 as select t2.*, t1.field as t1field
from t1
join t2 on t2.key=t1.key
update table2 set table2.field = t1field

I'm shaky on "with" syntax, so look it up, but that's the general idea.

[Back to original message]