ALTER TABLE - Rename a column

  • This is driving me nuts!

    I want to rename a column from read-more to read_more in my blog table

    I tried all this:

      ALTER TABLE blog RENAME COLUMN read-more to read_more;
      ALTER TABLE blog CHANGE COLUMN 'read-more' 'read_more' VARCHAR(255) NOT NULL;

    And I always get this!

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN read-more to read_more' at line 1

    I'm using MySQL 5.5

  • The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes:

    ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;
  • ALTER TABLE `blog` CHANGE  `read-more` `read_more` VARCHAR(255) NOT NULL;

    Above mentioned query is correct and there is no need to use "column" keyword and quotes around table and column name if you are using mysql database:

    ALTER TABLE blog CHANGE read-more read_more VARCHAR(255) NOT NULL;
  • Here's what worked for me:

    ALTER TABLE vm_list CHANGE `vm_notes]` vm_notes VARCHAR(255); 
    Query OK, 0 rows affected (0.01 sec) 
    Records: 0  Duplicates: 0  Warnings: 0

    Yes, I somehow got a column named "vm_notes]" in there.

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM