ALTER TABLE ... MODIFY COMMENT

CREATE TABLE table_with_comment
(
    `k` UInt64,
    `s` String
)
ENGINE = Memory()
COMMENT 'The temporary table';

ALTER TABLE table_with_comment 
MODIFY COMMENT 'new comment on a table';

SELECT comment 
FROM system.tables 
WHERE database = currentDatabase() AND name = 'table_with_comment';

┌─comment────────────────┐
│ new comment on a table │
└────────────────────────┘

ALTER TABLE table_with_comment MODIFY COMMENT '';

SELECT comment 
FROM system.tables 
WHERE database = currentDatabase() AND name = 'table_with_comment';

┌─comment─┐
│         │
└─────────┘