CODEWITHSUNDEEP
sqloracle-database
Leonardo Alves Machado
Leonardo Alves Machado

Reputation: 2837

Delete rows only if the table exists

I need to run a delete statement on my database if a table was previously created.

The problem is - I can't just run the delete statement, since the product is not on every client's productive environments - therefore, they don't have the table where I want to run the delete statement, and would end up with an error 00942. 00000 - "table or view does not exist".

An example:

I would like to run something like this:

IF EXISTS (TABLE TB_FIELD)
    DELETE FROM TB_FIELD WHERE ID = '213';

If there isn't a generic statement, I would like one that would run for Oracle databases

Upvotes: 0

Views: 3015

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Simplest way is to catch and ignore the "table not found" exception:

declare
  l_id number := 12345;
begin
  execute immediate 'delete tb_field where id=:1' using l_id;
exception
  when others then
    if sqlcode != -942 /*table or view does not exist*/ then
      raise;
    end if;
end;
/

Upvotes: 2

Jon Theriault
Jon Theriault

Reputation: 123

Here's one for Oracle. This assumes the current user owns the table. If you're updating someone else's table you'll need to swap out user_tables with dba_tables.

declare
    table_name_l    user_tables.table_name%type;
begin
    select table_name into table_name_l from user_tables where table_name = 'TB_FIELD';
    -- if we didn't raise an exception the table must exist 
    execute immediate 'delete from tb_field where id = :1' using '213';

    exception 
        when no_data_found then 
            -- do nothing - table doesn't exist
            null;
end;

Upvotes: 5

Related Questions