Delete rows only if the table exists










0














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










share|improve this question























  • Are you using SAP Hana or Oracle? Those are two very different database systems.
    – a_horse_with_no_name
    Nov 12 at 13:23










  • Possible duplicate of Oracle: If Table Exists
    – ikram
    Nov 12 at 13:27










  • I'm using both, depending on the client, @a_horse_with_no_name
    – Leonardo Alves Machado
    Nov 12 at 13:29















0














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










share|improve this question























  • Are you using SAP Hana or Oracle? Those are two very different database systems.
    – a_horse_with_no_name
    Nov 12 at 13:23










  • Possible duplicate of Oracle: If Table Exists
    – ikram
    Nov 12 at 13:27










  • I'm using both, depending on the client, @a_horse_with_no_name
    – Leonardo Alves Machado
    Nov 12 at 13:29













0












0








0







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










share|improve this question















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







sql oracle






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 at 2:27









Jeffrey Kemp

47.7k1187131




47.7k1187131










asked Nov 12 at 13:18









Leonardo Alves Machado

1,84661730




1,84661730











  • Are you using SAP Hana or Oracle? Those are two very different database systems.
    – a_horse_with_no_name
    Nov 12 at 13:23










  • Possible duplicate of Oracle: If Table Exists
    – ikram
    Nov 12 at 13:27










  • I'm using both, depending on the client, @a_horse_with_no_name
    – Leonardo Alves Machado
    Nov 12 at 13:29
















  • Are you using SAP Hana or Oracle? Those are two very different database systems.
    – a_horse_with_no_name
    Nov 12 at 13:23










  • Possible duplicate of Oracle: If Table Exists
    – ikram
    Nov 12 at 13:27










  • I'm using both, depending on the client, @a_horse_with_no_name
    – Leonardo Alves Machado
    Nov 12 at 13:29















Are you using SAP Hana or Oracle? Those are two very different database systems.
– a_horse_with_no_name
Nov 12 at 13:23




Are you using SAP Hana or Oracle? Those are two very different database systems.
– a_horse_with_no_name
Nov 12 at 13:23












Possible duplicate of Oracle: If Table Exists
– ikram
Nov 12 at 13:27




Possible duplicate of Oracle: If Table Exists
– ikram
Nov 12 at 13:27












I'm using both, depending on the client, @a_horse_with_no_name
– Leonardo Alves Machado
Nov 12 at 13:29




I'm using both, depending on the client, @a_horse_with_no_name
– Leonardo Alves Machado
Nov 12 at 13:29












2 Answers
2






active

oldest

votes


















5














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;





share|improve this answer




























    2














    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;
    /





    share|improve this answer




















      Your Answer






      StackExchange.ifUsing("editor", function ()
      StackExchange.using("externalEditor", function ()
      StackExchange.using("snippets", function ()
      StackExchange.snippets.init();
      );
      );
      , "code-snippets");

      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "1"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263044%2fdelete-rows-only-if-the-table-exists%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      5














      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;





      share|improve this answer

























        5














        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;





        share|improve this answer























          5












          5








          5






          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;





          share|improve this answer












          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;






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 12 at 13:39









          Jon Theriault

          11314




          11314























              2














              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;
              /





              share|improve this answer

























                2














                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;
                /





                share|improve this answer























                  2












                  2








                  2






                  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;
                  /





                  share|improve this answer












                  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;
                  /






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 13 at 2:22









                  Jeffrey Kemp

                  47.7k1187131




                  47.7k1187131



























                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263044%2fdelete-rows-only-if-the-table-exists%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Top Tejano songwriter Luis Silva dead of heart attack at 64

                      政党

                      天津地下鉄3号線