2

I want to delete a row from a table if that table exists.

Below is the delete statement

DELETE 
FROM UM_CLAIM_BEHAVIOR 
WHERE UM_TENANT_ID = ?

Can I extend this query to check the existence of the table or is there any other alternative to check existence of table before executing delete query?

According to comments it is not possible to give a platform-independent solution for this.

2
  • 3
    Why not just run the delete and catch any error in your application? There is generic solution to this.
    – user1822
    Commented Jan 16, 2015 at 7:53
  • @a_horse_with_no_name: thanks. I will try that way. catching and continue seems like a good solution.
    – lakshman
    Commented Jan 16, 2015 at 7:56

2 Answers 2

4

In SQL Server you could do something like this

IF EXISTS (
        SELECT 1
        FROM sys.objects AS o
        INNER JOIN sys.schemas AS s
            ON s.schema_id = o.schema_id
        WHERE CONCAT (s.NAME, '.', o.NAME) = 'dbo.UM_CLAIM_BEHAVIOR'
            AND o.type_desc = 'USER_TABLE'
        )
BEGIN
    DELETE T
    FROM UM_CLAIM_BEHAVIOR AS T
    WHERE UM_TENANT_ID = ?
END
2
  • thanks. but I searched for a platform independent one
    – lakshman
    Commented Jan 16, 2015 at 7:49
  • 5
    @lakshman: there is no "platform" independent way to do this. SQL Server is about the only DBMS where you can mix procedural code and "regualar" SQL code like that. For nearly every other DBMS you will need to write a stored procedure to achieve what you want.
    – user1822
    Commented Jan 16, 2015 at 7:52
1

You can do a platform-independant method, you just have to make a round trip to the server.

SELECT 'DELETE FROM ' || t.table || ' WHERE WHATEVER;'
FROM information_schema.tables
WHERE table_name = 'mytable';

Now you execute the whatever that returns. If the table exists, it'll return the query to delete the row. If not, it'll return NULL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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