Home » SQL & PL/SQL » SQL & PL/SQL » Where clause in SQL MERGE UPDATE statement
Where clause in SQL MERGE UPDATE statement [message #243990] Mon, 11 June 2007 03:48 Go to next message
bhavani_kuppa
Messages: 5
Registered: June 2007
Junior Member
Can use WHERE clause in SQL MERGE – UPDATE statement?


MERGE <target>
USING <source>
ON <condition>
WHEN MATCHED THEN
UPDATE SET ……. WHERE <condition>
WHEN NOT MATCHED THEN
INSERT ………;



Re: Where clause in SQL MERGE UPDATE statement [message #243992 is a reply to message #243990] Mon, 11 June 2007 03:56 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

No
You have the <using> clause for that
Re: Where clause in SQL MERGE UPDATE statement [message #244004 is a reply to message #243990] Mon, 11 June 2007 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Only in 10g.

Regards
Michel
Re: Where clause in SQL MERGE UPDATE statement [message #244313 is a reply to message #244004] Tue, 12 June 2007 05:34 Go to previous messageGo to next message
bhavani_kuppa
Messages: 5
Registered: June 2007
Junior Member
Hi,

Thanks for the reply.
Could you please send me syntax for the same in 10g.


Re: Where clause in SQL MERGE UPDATE statement [message #244330 is a reply to message #244313] Tue, 12 June 2007 06:29 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
http://tahiti.oracle.com

download the sql reference
Re: Where clause in SQL MERGE UPDATE statement [message #244336 is a reply to message #244313] Tue, 12 June 2007 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MERGE

Regards
Michel
Re: Where clause in SQL MERGE UPDATE statement [message #246175 is a reply to message #244336] Wed, 20 June 2007 03:32 Go to previous messageGo to next message
bhavani_kuppa
Messages: 5
Registered: June 2007
Junior Member
Hi,

I am using 10g R2 oracle version.
I have an issue with merge update clause.

table t1:
id Name
**********
1
2 orange

table t2:
id Name
**********
1 Apple
2 orange
3 mango

Requirement:
1. I should not update records which have no changes in name.
2. For a given id in t1, if name is null. That record should be deleted from t2.

Following is my statement:
merge into t2 using t1
on (t2.id = t1.id)
when matched then
update set t2.name = t1.name where t1.name <> t2.name
delete where name is null
when not matched then
insert values (t1.id, t1.name)

Problem
I am unable to delete record from t1 where name is null.
Is NULL not recognised in '<>' comparison?

Please let me know if this can be achived using merge statement.

Regards,
Bhavani
Re: Where clause in SQL MERGE UPDATE statement [message #246179 is a reply to message #246175] Wed, 20 June 2007 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try
merge into t2 using t1
on (t2.id = t1.id)
when matched then
  update set t2.name = t1.name 
    where nvl(t1.name,'X') <> nvl(t2.name,'X')
    delete where name is null
when not matched then 
  insert values (t1.id, t1.name)
/

Regards
Michel
Re: Where clause in SQL MERGE UPDATE statement [message #246185 is a reply to message #246179] Wed, 20 June 2007 04:05 Go to previous messageGo to next message
bhavani_kuppa
Messages: 5
Registered: June 2007
Junior Member
Hi,
Thanks for the reply.
I have used following statement.
merge into t2 using t1
on (t2.id = t1.id)
when matched then
update set t2.name = t1.name where t1.name <> t2.name or t1.name is null
delete where name is null
when not matched then
insert values (t1.id, t1.name)


Will NVL() take more time for processing or above statement is IS NULL and OR operator?
I need to work on about 2 million records in the actual scenario.

Regards,
Bhavani
Re: Where clause in SQL MERGE UPDATE statement [message #246194 is a reply to message #246185] Wed, 20 June 2007 04:35 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the version and indexes.
I think your way may be better in most cases.

Regards
Michel

[Updated on: Wed, 20 June 2007 04:36]

Report message to a moderator

Previous Topic: How to break column values
Next Topic: need column headers
Goto Forum:
  


Current Time: Sat Dec 10 01:14:55 CST 2016

Total time taken to generate the page: 0.06099 seconds