How to copy records from one table to another or itself in mysql

Ngày 23 tháng 10 năm 2011 Trương Chương Dương

-- Create temp table

DROP TABLE IF EXISTS table_tmp;

CREATE TABLE table_tmp LIKE <source_table_name>;

-- Copy data to temp table

INSERT table_tmp SELECT * FROM <source_table_name> WHERE <condition>;

-- If the id is AUTO_INCREMENT, remove this properties and set to null, if not, skip these lines

ALTER TABLE `table_tmp` CHANGE `id` `id` INT( 10 ) NOT NULL;

ALTER TABLE table_tmp DROP PRIMARY KEY;

ALTER TABLE `table_tmp` CHANGE `id` `id` INT( 10 ) NULL;

-- End delete AUTO_INCREMENT commands

-- Update some properties in the copied record if need, this line is example only ;
UPDATE table_tmp SET id=NULL, year = 2012, term = 1;

-- Copy new data to <desc_table_name>, may be same with <source_table_name>
INSERT <desc_table_name> SELECT * FROM table_tmp;

--Remove temp table
DROP TABLE IF EXISTS table_tmp;

Đang tải dữ liệu...
web site traffic statistics