'Using MYSQL, How do I query a table only if it exists already?

Im writing a migration and Im trying to query a table only if it exists in the db. In some envs it will exist and in others it wont which is why i want to check if it exists. The table of interest is called OLD_PASSWORD. I do the following.

SELECT ID, PASSWORD
      FROM OLD_PASSWORD;
      WHERE EXISTS 
(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'OLD_PASSWORD);

This fails '.dbcSQLSyntaxErrorException: Table "USER_OLD_PASSWORDS" not found;' which makes sense because it wont exist in some envs.

I also tried something like

IF (EXISTS (SELECT *
             FROM   INFORMATION_SCHEMA.TABLES
             WHERE  TABLE_NAME = 'OLD_PASSWORD'))
  BEGIN
      SELECT ID,
             PASSWORD
      FROM   OLD_PASSWORD
  END

but it's not compatible with mysql.

mysql


Solution 1:[1]

Best approach was to query INFORMATION_SCHEMA.TABLES from Java, and then do the query from OLD_PASSWORD if it returns something.

Solution 2:[2]

You forgot the ' sign at the end of query before the ).

Nonetheless, this is cartesian select. Use in instead. i.e

password in (select password  ...) etc.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 acon24
Solution 2 Eitan

Related Questions