7

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?

8

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>
| improve this answer | |
4

According to the documentation, the IGNORE option will treat errors as warnings.

DELETE IGNORE ...

The IGNORE keyword causes MySQL to ignore all errors during the process of deleting rows. (Errors encountered during the parsing stage are processed in the usual manner.) Errors that are ignored due to the use of IGNORE are returned as warnings.

I'm not sure whether MySQL would treat a non-existent table as "an error in the parsing stage". If it does, you can query the information_schema for a table name. Something along these lines should work.

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your database name'
AND table_name = 'your table name';

Temporary tables don't appear in the information_schema views. If your table is a temporary table, then you have a bug in your code. You're dropping the table before you're finished with it.

| improve this answer | |
  • Thanks a lot for the quick response. I tried using IGNORE to no avail, I guess the non-existent table is found during the parsing stage. How would I construct the SELECT and DELETE as one query? From what I understand I can't use IF statements because MySQL only supports them as stored procedures. – GTE Oct 9 '11 at 20:14
  • 2
    You can't do it in a single statement. Why don't you want to handle the error in Ruby? (It doesn't make sense to not handle errors in Ruby.) – Mike Sherrill 'Cat Recall' Oct 9 '11 at 22:32
  • tip on the database name, you can just use DATABASE() to use the currently selected schema. – Garr Godfrey Oct 21 '16 at 0:40
  • DELETE IGNORE does not silence the error on a non-existent table. #1146 - Table 'nonexistent_tablename' doesn't exist – mickmackusa Sep 17 '18 at 7:01
3

I would test the table exists first, by running a query such as;

SHOW TABLES LIKE 'table1';

And then perform the delete based on the results of that query. If the table exists, a single record is returned containing the table name.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.