How to create duplicate table?
by Sanju[ Edit ] 2008-07-29 18:09:52
You can create a duplicate table by copying an existing mysql table to a new table.
Syntax:
CREATE TABLE table_name2 LIKE table_name1;
INSERT table_name2 SELECT * FROM table_name1;
>> The first command creates the new table table_name2 by duplicating the structure of the existing table table_name1.
>> The second command copies the data from old(table_name1) to new(table_name2).
Creating a duplicate table helps when you use same table name in subquery.
Example:
mysql> update test set date1=DATE_ADD(date1, interval 1 MONTH) where
id IN (select id from test as df where id like '1%');
I have got an error,
ERROR 1093 (HY000): You can't specify target table 'test' for update in FROM clause
Here i have created a duplicate table test1 and used the query as,
mysql> update test set date1=DATE_ADD(date1, interval 1 MONTH) where id IN (select id from test1 as df where id like '1%');
