Need to Check if a Table Exists Before Deleting a Record

I have the following SQL query in my Ruby app:

sql = "DELETE FROM `#{database}`.`table1` WHERE `same_id` = #{some_id};"

The problem is that in rare circumstances table1 might not exist. I need to construct this query so even if the table does not exist SQL does not throw an error.

How can I implement this?

You should probably use a stored procedure to to do this:

DELIMITER $ 

DROP PROCEDURE IF EXISTS `test`.`DeleteByID` $ 
CREATE PROCEDURE `test`.`DeleteByID` (db VARCHAR(64),tb VARCHAR(64),id_to_delete INT) 
BEGIN 
    DECLARE FoundCount INT;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.tables
    WHERE table_schema = db
    AND table_name = tb;

    IF FoundCount = 1 THEN
        SET @sql = CONCAT('DELETE FROM ',db,'.',tb,' WHERE id=',id_to_delete); 
        PREPARE stmt FROM @sql; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt;
    END IF;

END $ 

DELIMITER ;

All you do in your code is call the stored procedure. For example, to delete ID 128 from drupaldb.comments:

CALL test.DeleteByID('drupaldb','comments',128);

Give it a Try !!!

Another variation on this would be to sculpt the query directly:

set @given_db = 'drupaldb';
set @given_tb = 'comments';
set @given_id = 128;
set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE id=',@given_id);
set @evil_sql = 'SELECT 1';
SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
FROM
(
    SELECT COUNT(1) table_exists
    FROM information_schema.tables 
    WHERE table_schema=@given_db
    AND table_name=@given_tb
) A;
PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;

NOTE: If the table does not exist, the query executed is SELECT 1.

Here is a sample that shows if the table does not exist:

mysql>     set @given_db = 'drupaldb';
Query OK, 0 rows affected (0.00 sec)

mysql>     set @given_tb = 'comments';
Query OK, 0 rows affected (0.00 sec)

mysql>     set @given_id = 128;
Query OK, 0 rows affected (0.00 sec)

mysql>     set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE id=',@given_id);
Query OK, 0 rows affected (0.00 sec)

mysql>     set @evil_sql = 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)

mysql>     SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
    ->     FROM
    ->     (
    ->         SELECT COUNT(1) table_exists
    ->         FROM information_schema.tables
    ->         WHERE table_schema=@given_db
    ->         AND table_name=@given_tb
    ->     ) A;
Query OK, 1 row affected (0.00 sec)

mysql>     PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Now, here is a sample where the table exists:

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> grant all on *.* to 'rolando'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> set @given_db = 'mysql';
Query OK, 0 rows affected (0.00 sec)

mysql> set @given_tb = 'user';
Query OK, 0 rows affected (0.00 sec)

mysql> set @given_id = 'rolando';
Query OK, 0 rows affected (0.00 sec)

mysql> set @good_sql = CONCAT('DELETE FROM ',@given_db,'.',@given_tb,' WHERE user=''',@given_id,'''');
Query OK, 0 rows affected (0.00 sec)

mysql> set @evil_sql = 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(table_exists=1,@good_sql,@evil_sql) INTO @DeleteSQL
    -> FROM
    -> (
    ->     SELECT COUNT(1) table_exists
    ->     FROM information_schema.tables
    ->     WHERE table_schema=@given_db
    ->     AND table_name=@given_tb
    -> ) A;
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE stmt FROM @DeleteSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.02 sec)
Statement prepared

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select count(1) from mysql.user where user='rolando';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>

StackExchange Database Administrators Q#6634, answer score: 8