Delete records from table if table exists

Question

I have a lot of delete queries but in some production databases some table is not found. I need simple decision on this problem.

Queries like

DELETE b.* FROM `table` b
JOIN `another_table` u
ON u.key2 = b.key
WHERE u.key3 <> ?

I need something like:

IF TABLE `table` EXISTS DELETE b.* FROM `table` b ...

score 4 · Accepted Answer · answered Jul 31 '13 at 12:08

If you need to delete the rows of a table if that latter exists, you will have to rely on a custom stored procedure I think. something like that:

-- Define the procedure
CREATE PROCEDURE delete_if_exists(IN tbl CHAR(80))
BEGIN
    IF @tbl IN (select table_name from information_schema.tables where table_schema = "db_2_44afb7")
    THEN
        SET @query = CONCAT("DELETE FROM ", @tbl);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
    END IF;
END //

-- use it
CALL delete_if_exists("mytable");
CALL delete_if_exists("myothertable");

See http://sqlfiddle.com/#!2/2ee190/3 for an example.
Of course, you could adjust the "DELETE FROM",... part to fit with your specific needs.

score 1 · Answer 2 · edited May 23 '17 at 11:45

1

You can enclose the SELECT query on the table before using the DROP...

IF EXISTS(SELECT table.records FROM table WHERE ...)
      DELETE records_names FROM ...

From what it sounds like IF EXISTS(select query here) before your delete command is what you're looking for? I'm a postgresql guy so please excuse the SQL errors if they exist.

Delete records from table if table exists

2 Answers2