0

trying to drop the index if it exists

checking if the index exists- in the inner query

ALTER TABLE tableA DROP INDEX indexname WHERE (SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS  WHERE TABLE_NAME = 'tableA' AND INDEX_NAME = 'indexname' AND INDEX_SCHEMA='schemaA')=1;

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 'WHERE (SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = tableA ' at line 1

in mysql version 5.7

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Dileep Domakonda
  • 19
  • 3
  • 1
    ALTER TABLE syntax does not allow any subqueries. Execute unconditionally and ignore error if occured. – Akina Nov 17 '20 at 13:06
  • Does this answer your question? [How can I employ "if exists" for creating or dropping an index in MySQL?](https://stackoverflow.com/questions/2480148/how-can-i-employ-if-exists-for-creating-or-dropping-an-index-in-mysql) – P.Salmon Nov 17 '20 at 13:08
  • hi @Akina okay, how to do that ? – Dileep Domakonda Nov 17 '20 at 13:09
  • Simply `DROP INDEX indexname ON tablename;`. – Akina Nov 17 '20 at 13:12
  • For more information on how to interpret MySQL's 1064 syntax errors, [please read this](https://stackoverflow.com/tags/mysql-error-1064/info). – O. Jones Nov 17 '20 at 13:52

0 Answers0