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)
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
what happened to the global index when I truncate the data in one of the partition?
What privilege is needed for a user to create tables in oracle?
What suggestions do you have to reduce the network traffic?
How many types of tables supported by oracle?
master table and child table performances and comparisons in Oracle ?
What are the numeric comparison operations?
1) Does oracle have any table which contain all the exceptions and it's code internally?
Briefly explain what is literal? Give an example where it can be used?
Which environment variables are absolutely critical in order to run the OUI?
How to rename a column in an existing table?
Please explain oracle left join with an example?
Can a Tablespace hold objects from different Schemes ?