Mysql – pass data from deleted table to another

database-designMySQL

This is the situation:

I have a table, with name TABLE_1 I would like delete TABLE_1 and then create another table with the same name and some of its columns.

TABLE_1 has records, but I need to pass it to the new table TABLE_1

What is the the most effective way to to this?

UPDATE

old TABLE_1:
    ID int(11) NOT NULL
    ACTIVE int(11) NOT NULL
    SENT_DATE DATETIME
    SENT INT(11) NOT NULL
    DESCRIPTION VARCHAR(50)


new TABLE_1:
    ID_TABLE_1 int(11) NOT NULL AUTO_INCREMENT
    STATUS TINYINT(1) NOT NULL DEFAULT(1)
    SENT_DATE DATETIME
    SENT TINYINT(1) DEFAULT(0)

In the ACTIVE and SENT columns are stores just 1 or 0. The relation between columns are (from old to new):

* ID -> ID_TABLE_1
* ACTIVE -> STATUS
* SENT_DATE -> SENT_DATE
* SENT -> SENT

Best Answer

If you want to keep the existing rows and don't want the table renamed but only a few columns altered/changed/dropped, you can do this with a single ALTER TABLE statement. This will only fail if there is no index on (id):

ALTER TABLE table_1
   CHANGE COLUMN id 
      id_table_1 int NOT NULL AUTO_INCREMENT,
-- ADD PRIMARY KEY 
--    (id_table_1),
   CHANGE COLUMN active 
      status TINYINT NOT NULL DEFAULT 1,
   CHANGE COLUMN sent
      sent TINYINT DEFAULT 0,
   DROP COLUMN description ;

Tested at SQL-Fiddle

Related Question