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
sql oracle
add a comment |
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
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.
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
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
sql oracle
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
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
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
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;
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;
/
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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;
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;
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;
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
11314
add a comment |
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;
/
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;
/
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;
/
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
47.7k1187131
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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