Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks in advance.

share|improve this question

3 Answers 3

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

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.

share|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

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.

share|improve this answer
    
Simple and effective. I like it :) –  TechieGurl Oct 10 '11 at 14:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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