Search code examples
pythonmysql

How to drop columns in a table, where column names are "index" and " Unnamed: 0" in mysql

I imported a dataframe via python to mysql server as a table (table-name = tips). During the process, I accidentally add columns named "index" and "Unnamed: 0" to mysql server. I tried different ways to delete the columns like below.

alter table tips drop 'index';
alter table tips drop column 'index';
alter table tips drop [index];
alter table tips drop ['index'];

For all the statements above for column "index" below is the error.

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 ''index'' at line 1

I tried the same for 'Unnamed: 0'

alter table tips drop 'Unnamed: 0';
alter table tips drop column 'Unnamed: 0';
alter table tips drop [Unnamed: 0];
alter table tips drop ['Unnamed: 0'];

For all the statements above for "Unnamed: 0" below is the error.

 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 ''Unnamed: 0'' at line 1
Solution

  • Column names need to be quoted inside backticks (`) when they contain reserved words or invalid characters. Use this instead:

    alter table tips drop column `index`, drop column `Unnamed: 0`

    Demo on dbfiddle