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

edited Nov 13 at 2:27

Jeffrey Kemp

47.7k1187131

asked Nov 12 at 13:18

Leonardo Alves Machado

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

add a comment |

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

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

edited Nov 13 at 2:27

Jeffrey Kemp

47.7k1187131

asked Nov 12 at 13:18

Leonardo Alves Machado

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

add a comment |

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

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

edited Nov 13 at 2:27

Jeffrey Kemp

47.7k1187131

asked Nov 12 at 13:18

Leonardo Alves Machado

1,84661730

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

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

edited Nov 13 at 2:27

Jeffrey Kemp

47.7k1187131

asked Nov 12 at 13:18

Leonardo Alves Machado

1,84661730

edited Nov 13 at 2:27

Jeffrey Kemp

47.7k1187131

asked Nov 12 at 13:18

Leonardo Alves Machado

1,84661730

edited Nov 13 at 2:27

Jeffrey Kemp

47.7k1187131

edited Nov 13 at 2:27

Jeffrey Kemp

47.7k1187131

edited Nov 13 at 2:27

Jeffrey Kemp

47.7k1187131

asked Nov 12 at 13:18

Leonardo Alves Machado

1,84661730

asked Nov 12 at 13:18

Leonardo Alves Machado

1,84661730

asked Nov 12 at 13:18

Leonardo Alves Machado

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

add a comment |

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

add a comment |

2 Answers
2

active

oldest

votes

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;

answered Nov 12 at 13:39

Jon Theriault

11314

add a comment |

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

answered Nov 13 at 2:22

Jeffrey Kemp

47.7k1187131

add a comment |

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

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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;

answered Nov 12 at 13:39

Jon Theriault

11314

add a comment |

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;

answered Nov 12 at 13:39

Jon Theriault

11314

add a comment |

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;

answered Nov 12 at 13:39

Jon Theriault

11314

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;

answered Nov 12 at 13:39

Jon Theriault

11314

answered Nov 12 at 13:39

Jon Theriault

11314

answered Nov 12 at 13:39

Jon Theriault

11314

answered Nov 12 at 13:39

Jon Theriault

11314

add a comment |

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

answered Nov 13 at 2:22

Jeffrey Kemp

47.7k1187131

add a comment |

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

answered Nov 13 at 2:22

Jeffrey Kemp

47.7k1187131

add a comment |

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

answered Nov 13 at 2:22

Jeffrey Kemp

47.7k1187131

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

answered Nov 13 at 2:22

Jeffrey Kemp

47.7k1187131

answered Nov 13 at 2:22

Jeffrey Kemp

47.7k1187131

answered Nov 13 at 2:22

Jeffrey Kemp

47.7k1187131

answered Nov 13 at 2:22

Jeffrey Kemp

47.7k1187131

add a comment |

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Myujth