To exchange partition p0  in table e  with table e2 , you can use ALTER TABLE , as shown here: 

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; Query OK, 0 rows affected (0.04 sec) 

More precisely, the statement just issued causes any rows found in the partition to be swapped with those found in the table. You can observe how this has happened by querying the INFORMATION_SCHEMA.PARTITIONS  table, as before. The table row that was previously found in partition p0  is no longer present: 

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.00 sec) 

If you query table e2 , you can see that the “missing”  row can now be found there: 

mysql> SELECT * FROM e2; +----+-------+-------+ | id | fname | lname | +----+-------+-------+ | 16 | Frank | White | +----+-------+-------+ 1 row in set (0.00 sec) 

The table to be exchanged with the partition does not necessarily have to be empty. To demonstrate this, we first insert a new row into table e , making sure that this row is stored in partition p0  by choosing an id column value that is less than 50, and verifying this afterward by querying the PARTITIONS  table: 

mysql> INSERT INTO e VALUES (41, "Michael", "Green"); Query OK, 1 row affected (0.05 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.00 sec) 

Now we once again exchange partition p0  with table e2  using the same ALTER TABLE  statement as previously: 

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; Query OK, 0 rows affected (0.28 sec) 

The output of the following queries shows that the table row that was stored in partition p0  and the table row that was stored in table e2 , prior to issuing the ALTER TABLE  statement, have now switched places: 

mysql> SELECT * FROM e; +------+-------+-------+ 

