Home » SQL & PL/SQL » SQL & PL/SQL » UPDATING AND INSERTING DATA IN A TABLE IDENTICAL TO OTHER TABLE
UPDATING AND INSERTING DATA IN A TABLE IDENTICAL TO OTHER TABLE [message #305088] Sat, 08 March 2008 06:09 Go to next message
kunaldurve
Messages: 1
Registered: March 2008
Junior Member
i have created two tables identical in nature
as follows :
1) create table emp(empid varchar2(256)primary key,empname varchar2(500),salary integer);

2) create table emp1(empid varchar2(256)primary key,empname varchar2(500),salary integer);


emp1 is replica of emp table . i want to check if empid of both table matches then updatation of record in emp1 should be done to match with the record in emp table for that particular empid.

otherwise if it is not same then insert the records for the empid which is not present in emp1.


i have tried this option



CREATE OR REPLACE procedure dummy_proc
is

v_empid emp.empid%type;
v_empname emp.empname%type;
v_salary emp.salary%type;
v_empid1 emp1.empid%type;
v_empname1 emp1.empname%type;
v_salary1 emp1.salary%type;
cursor emp_cursor is
select * from emp ;

cursor emp1_cursor is
select * from emp1;




begin



if not emp_cursor%ISOPEN then
open emp_cursor;
fetch emp_cursor into v_empid,v_empname,v_salary;
if not emp1_cursor%ISOPEN THEN
open emp1_cursor;
fetch emp1_cursor into v_empid1,v_empname1,v_salary1;


loop
if v_empid1 = v_empid then
delete from emp1 where empid = v_empid;
update emp1
set empid = v_empid,
empname = v_empname,salary = v_salary ;
else
insert into emp1 values('v_empid','v_empname',v_salary);
end if;

END loop;
end if;
close emp1_cursor;
end if;
close emp_cursor ;



end dummy_proc;
/


in this im getting the following error

ORA-00001: unique constraint (SCHEMA.SYS_C0054769) violated
ORA-06512: at "SCHEMA.DUMMY_PROC", line 39
ORA-06512: at line 2


can u please suggest how to achieve my objective.
Re: UPDATING AND INSERTING DATA IN A TABLE IDENTICAL TO OTHER TABLE [message #305092 is a reply to message #305088] Sat, 08 March 2008 06:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will sincerely suggest you to do the following :
a) Read the sql reference manual
b) Read the pl/sql reference manual
c) Go to step 1

http://tahiti.oracle.com/
Regards

Raj
Re: UPDATING AND INSERTING DATA IN A TABLE IDENTICAL TO OTHER TABLE [message #305093 is a reply to message #305092] Sat, 08 March 2008 06:35 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
in addition to what Raj has told you should explore the MERGE statement.

regards,
Re: UPDATING AND INSERTING DATA IN A TABLE IDENTICAL TO OTHER TABLE [message #305102 is a reply to message #305088] Sat, 08 March 2008 09:00 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
DON'T PUT YOUR TITLE IN UPPER CASE.
It is not an expert question.
It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Follow them.

Regards
Michel

[Updated on: Sat, 08 March 2008 09:00]

Report message to a moderator

Previous Topic: Procedure in ksh
Next Topic: help with PL SQL - error "invalid datatype"
Goto Forum:
  


Current Time: Thu Dec 08 02:37:01 CST 2016

Total time taken to generate the page: 0.11451 seconds