Home » SQL & PL/SQL » SQL & PL/SQL » insert and update in single query
insert and update in single query [message #232952] Tue, 24 April 2007 04:29 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

I just want to confirm that there is any way in oracle 9i which I can insert or update the record on table in single query like ‘MERGE’ statement in oracle 9i..


--Yash
Re: insert and update in single query [message #232953 is a reply to message #232952] Tue, 24 April 2007 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can confirm there is a MERGE statement in Oracle 9i as it is in Oracle 9i.

Regards
Michel
Re: insert and update in single query [message #232957 is a reply to message #232953] Tue, 24 April 2007 04:37 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Sorry Michel !! i did not get you ...

Actually we have scheduled cronjob in unix server which update every night employee table in my project. so my Perl script which getting data from ldap server it checks first employee id is exit or not that bases if else block update or insert the ldap record
In employee table so i am asking there is any way i can merge both if and else block queries in single query..


--Yash
Re: insert and update in single query [message #232958 is a reply to message #232957] Tue, 24 April 2007 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yash,

I did not get you.
This is SQL a PL/SQL forum, why do ask something about ldap?
Your question was:
Quote:
I just want to confirm that there is any way in oracle 9i which I can insert or update the record on table in single query like ‘MERGE’ statement in oracle 9i.

Yes, there is statement that can insert or update the record on table in single query and it is MERGE.
But why posting this question as you know the answer?

Regards
Michel
Re: insert and update in single query [message #232973 is a reply to message #232958] Tue, 24 April 2007 05:52 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Michel,

I am asking sql and pl/sql question only, might be I am not asking in right manner Sorry for that…

I am takeing data from LDAP and inserting in oracle ..so my question is based on insert andUpdate in oracle ..let say I got following data from ldap

100,’xxx’……
Now I want to check 100 is exist in employee table or not .. let say I used following dummy

Select count(*) into flag from employee where employee_id=100

If flag=1 then 

Update employee 

Else

Insert employee

End if;
 


I am trying to wrote the query for the above logic is as:

MERGE INTO employee a
  USING employee b 
    ON ( b.employee_id=a.employee_id and b.employee_id=100)
  WHEN MATCHED THEN
    UPDATE SET a.last_name = 'abc' 
    
  WHEN NOT MATCHED THEN
    INSERT (last_name ,employee_id,email,job_id )
    VALUES ('xxxxx' ,100,'xxx','AD_PRES')


But I was suppose that query should insert one record but it has inserted 21 times to same record

Can u suggest how I get the solution ..


--Yash
Re: insert and update in single query [message #232974 is a reply to message #232973] Tue, 24 April 2007 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MERGE INTO employee a
  USING dual b 
    ON (a.employee_id=100)
  WHEN MATCHED THEN
    UPDATE SET a.last_name = 'abc' 
  WHEN NOT MATCHED THEN
    INSERT (last_name ,employee_id,email,job_id )
    VALUES ('xxxxx' ,100,'xxx','AD_PRES')

Regards
Michel
Re: insert and update in single query [message #232978 is a reply to message #232974] Tue, 24 April 2007 06:19 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Michel it is working
Re: insert and update in single query [message #232984 is a reply to message #232978] Tue, 24 April 2007 06:27 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Precise question gets precise answer. Smile

Regards
Michel
Previous Topic: ORA-00942: table or view does not exist: v$locked_object used in Fucntion
Next Topic: Getting Column Names Updated When An Update Statement Fires
Goto Forum:
  


Current Time: Wed Dec 07 22:34:23 CST 2016

Total time taken to generate the page: 0.06449 seconds