Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Othe

Table Has C1 And C2 Column If Exits any record in c1 then
Update c2 record Otherwise insert new record in the C1 And
C2 (Using Procedure)

Question Posted / akshay

4 Answers
7560 Views
Thermotech, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new rec..

Answer / ron

use oracle's new feature called upsert :

merge t1
using (select * t1) t2
on (t1.col1=t2.col1)
when matched then
update set col2='value'
when not matched then
insert into (col1, col2) values ('val1', 'val2');

Is This Answer Correct ?

3 Yes

0 No

Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new rec..

Answer / dinesh a.

select * from x;

C1 C2
----- ----------
1
2
3
4
5
6
7
8
9
10

1 create or replace procedure updt_x is
2 cnt number(4);
3 begin
4 select count(1) into cnt from x where c1 is not null;
5 if cnt > 0 then
6 update x
7 set c2=10;
8 else
9 insert into x
10 values(1,2);
11 end if;
12 commit;
13* end;

SQL> execute updt_x;

PL/SQL procedure successfully completed.

SQL> select * from x;

C1 C2
---------- ----------
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10
10 10

10 rows selected.

SQL> delete from x;

10 rows deleted.

SQL> commit;

PL/SQL procedure successfully completed.

SQL> select * from x;

C1 C2
---------- ----------
1 2

Is This Answer Correct ?

2 Yes

0 No

Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new rec..

Answer / ramprasad.s

Hi Answer to Your question
IS
create or replace procedure samp(cname IN sample.ename%type,
cno IN sample.eno%type)
AS
ccount number;
begin
select count(*) INTO ccount from sample;
IF ccount = 0 then
update sample set ename = cname;
else
insert into sample values(cname, cno);
END IF;
END;

Sample Table is
ENAME ENO
100

Is This Answer Correct ?

1 Yes

0 No

Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new rec..

Answer / senthilkumar

create or replace procedure update is

declare

cursor updt is

select cl from table;

num number(4);

begin

open updt;

fetch updt into num;

if num != NULL

then
update table set c2 = num or(desired data);

else
insert into table values(value,value);

exception

when others then

DBMS_OUTPUT.PUT_LINE('SQLERR'|| ''||SQLERRM);

end;

Is This Answer Correct ?

0 Yes

0 No

Post New Answer