Remove two tables at the same time : Delete Table « Table Index « SQL / MySQL






  1. SQL / MySQL
  2. Table Index
  3. Delete Table

Remove two tables at the same time

/*

mysql> select * from timeTable;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2001 |    01 |   01 |
| 2002 |    06 |   20 |
| 2003 |    05 |   30 |
| 2004 |    02 |   02 |
| 2005 |    04 |   23 |
| 2006 |    03 |   23 |
| 2001 |    01 |   01 |
| 2002 |    06 |   20 |
| 2003 |    05 |   30 |
| 2004 |    02 |   02 |
| 2005 |    04 |   23 |
| 2006 |    03 |   23 |
+------+-------+------+
12 rows in set (0.01 sec)

mysql> INSERT INTO copy_timeTable
    -> SELECT * FROM timeTable
    -> WHERE year=2001;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_t    |
+----------------+
| copy_timetable |
| sales          |
| timetable      |
+----------------+
3 rows in set (0.01 sec)

mysql> DROP TABLE copy_TimeTable, TimeTable;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------+
| Tables_in_t |
+-------------+
| sales       |
+-------------+
1 row in set (0.00 sec)


*/  
Drop table timeTable;
Drop table copy_timeTable;


CREATE TABLE timeTable (
    year YEAR(4), 
    month INT(2) UNSIGNED ZEROFILL,
    day INT(2) UNSIGNED ZEROFILL
);

CREATE TABLE copy_timeTable (
    year YEAR(4), 
    month INT(2) UNSIGNED ZEROFILL,
    day INT(2) UNSIGNED ZEROFILL
);


INSERT INTO timeTable VALUES(2001,1,1),
                            (2002,6,20),
                            (2003,5,30),
                            (2004,2,2),
                            (2005,4,23),
                            (2006,3,23);

select * from timeTable;

INSERT INTO copy_timeTable
SELECT * FROM timeTable
WHERE year=2001;

show tables;
  
DROP TABLE copy_TimeTable, TimeTable;


show tables;








Related examples in the same category

1.How to delete a table
2.Create, insert and drop a table