0

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.

  • 1
    Why not just run the delete and catch any error in your application? There is generic solution to this. – a_horse_with_no_name Jan 16 '15 at 7:53
  • @a_horse_with_no_name: thanks. I will try that way. catching and continue seems like a good solution. – lakshman Jan 16 '15 at 7:56
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
  • thanks. but I searched for a platform independent one – lakshman Jan 16 '15 at 7:49
  • 4
    @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. – a_horse_with_no_name Jan 16 '15 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, privacy policy and cookie policy

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