Records: 0 Duplicates: 0 Warnings: 0 

The number following COALESCE PARTITION  is the number of partitions to merge into the remainder—in other words, it is the number of partitions to remove from the table. 

Attempting to remove more partitions than are in the table results in an error like this one: 

mysql> ALTER TABLE clients COALESCE PARTITION 18; ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead 

To increase the number of partitions for the clients  table from 12 to 18, use ALTER TABLE ... ADD PARTITION  as shown here: 

ALTER TABLE clients ADD PARTITION PARTITIONS 6; 

## 24.3.3 Exchanging Partitions and Subpartitions with Tables 

In MySQL 8.0, it is possible to exchange a table partition or subpartition with a table using ALTER TABLE pt  EXCHANGE PARTITION p  WITH TABLE nt , where pt  is the partitioned table and p  is the partition or subpartition of pt  to be exchanged with unpartitioned table nt , provided that the following statements are true: 

1. Table nt  is not itself partitioned. 

2. Table nt  is not a temporary table. 

3. The structures of tables pt  and nt  are otherwise identical. 

4. Table nt  contains no foreign key references, and no other table has any foreign keys that refer to nt . 

5. There are no rows in nt  that lie outside the boundaries of the partition definition for p . This condition does not apply if WITHOUT VALIDATION  is used. 

6. For InnoDB  tables, both tables use the same row format. To determine the row format of an InnoDB table, query INFORMATION_SCHEMA.INNODB_TABLES . 

7. nt  does not have any partitions that use the DATA DIRECTORY  option. This restriction is lifted for InnoDB  tables in MySQL 8.0.14 and later. 

In addition to the ALTER , INSERT , and CREATE  privileges usually required for ALTER TABLE  statements, you must have the DROP  privilege to perform ALTER TABLE ... EXCHANGE PARTITION . 

You should also be aware of the following effects of ALTER TABLE ... EXCHANGE PARTITION : 

•Executing ALTER TABLE ... EXCHANGE PARTITION  does not invoke any triggers on either the partitioned table or the table to be exchanged. 

•Any AUTO_INCREMENT  columns in the exchanged table are reset. 

•The IGNORE  keyword has no effect when used with ALTER TABLE ... EXCHANGE PARTITION . 

The syntax for ALTER TABLE ... EXCHANGE PARTITION  is shown here, where pt  is the partitioned table, p  is the partition (or subpartition) to be exchanged, and nt  is the nonpartitioned table to be exchanged with p : 

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt ; 

Optionally, you can append WITH VALIDATION  or WITHOUT VALIDATION . When WITHOUT VALIDATION  is specified, the ALTER TABLE ... EXCHANGE PARTITION  operation does not perform 

