[RESOLVED] what's the best way of moving cols from 1 table to another ?

steamPunk

I have some cols in 1 table that I would like to 'move' to another table because i stupidly didn't think some aspects through properly before setting up the tables

Obviously it's dead simple to create the cols in table 2 - but i need to copy the date across from table 1 to table 2 and ensure it goes in the right row .

i could select all of table 1 then loop through the results and use the 'p_id' to insert the data in the correct row in table 2 but there has to be a simpler way of doing this

could someone point me in the right direction, please ?

thanks

steamPunk

doesn't matter - i did it the long way round, but i'd be interested to learn a better way

bradgrafelman

UPDATE new_table, old_table SET new_table.col = old_table.col WHERE new_table.p_id = old_table.p_id;

ALTER TABLE old_table DROP COLUMN col;

That's assuming you're using a DBMS for the job, namely MySQL. Mileage may vary for different DBMS's.

steamPunk

okayyyyy

I'd never have thought of putting both tables in the UPDATE :

UPDATE new_table, old_table SET ...

so it's really quite simple !

thanks