CODEWITHSUNDEEP
mysqlif-statementexists
PeteGO
PeteGO

Reputation: 5791

MySql - only update some rows if the table exists - do not want an error thrown

I want to run an update query.

The query will be run against multiple databases - not every database will have the table.

I don't want the update to be attempted if the table does not exist. I don't want any error to be thrown - I just want the update to be ignored.

Any ideas?

EDIT: just to be clear - the query is executed in an automated deployment - no human interaction possible.

EDIT2: the logic to say whether the update should run or not will need to be in the MySql query itself. This is not being run through a command prompt or batch or managed code.

Upvotes: 1

Views: 2513

Answers (3)

spencer7593
spencer7593

Reputation: 108420

To do this in native MySQL (like in a mysql script), you could use a stored procedure.

This would be appropriate for a one-off administrative type function, you wouldn't want to do this as part of an application. (Then again, if this were part of an application, you would have the conditional logic to control whether an UPDATE is performed, or to catch and handle the exception if the table doesn't exist.)

Here's an example of a stored procedure that uses a CONTINUE HANDLER to catch error 1146 (table does not exist) and swallow it, so that no error is returned to the caller.

DELIMITER //
DROP PROCEDURE IF EXISTS `table_doesnt_exist_handler`//
CREATE DEFINER=`root`@`%` PROCEDURE `table_doesnt_exist_handler`()
BEGIN
  DECLARE table_doesnt_exist BOOLEAN;
  DECLARE CONTINUE HANDLER FOR 1146 SET table_doesnt_exist = TRUE;

  UPDATE table_doesnt_exist SET foo = 1 WHERE foo = 1;

END//
CALL `table_doesnt_exist_handler`()//
DROP PROCEDURE IF EXISTS `table_doesnt_exist_handler`//
DELIMITER ;

Another option is to test for the existence of the table, and conditionally running the statement. In native MySQL, again, this would need to be done inside a stored procedure.

DELIMITER //
DROP PROCEDURE IF EXISTS `table_doesnt_exist_approach_2`//
CREATE DEFINER=`root`@`%` PROCEDURE `table_doesnt_exist_approach_2`()
BEGIN
  SELECT @table_exists := COUNT(1) AS table_exists
    FROM information_schema.tables
   WHERE table_schema = DATABASE() 
     AND table_name = 'table_doesnt_exist';
  IF  @table_exists > 0 THEN
    UPDATE table_doesnt_exist SET foo = 2 WHERE foo = 1;
  END IF;
END//

CALL `table_doesnt_exist_approach_2`()//
DROP PROCEDURE IF EXISTS `table_doesnt_exist_approach_2`//
DELIMITER ;

My preference would be to go with the CONTINUE HANDLER.

Upvotes: 2

Razvan
Razvan

Reputation: 10093

You can first run a query to see if that table exists and then run the actual query.

Ex: 

table_exists_query = Select count(*) from INFORMATION_SCHEMA.tables where table_name='xxxx';

if(run_query(table_exists_query)==1) run the actual query

Upvotes: 0

Shawn
Shawn

Reputation: 3369

You could do something like do a describe on the table name and if the result is empty then dont run the update. You can do this in a short script, although I'm not sure what language your using. You could also probably manage it in a stored procedure if your looking strictly DB here.

Upvotes: 0

Related Questions