We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
6 days, 8 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Help with a Merge statement in oracle
Posted on 2011-03-08
Hi,
Below is the merge statement I was using to Update and insert data into a table. But the problem is the conditions in the Update statement are not working. I mean rows are not being updated, but when I remove one of the condition then it works.
MERGE INTO table a
USING table b
ON (b.id = a.id)
WHEN MATCHED
THEN
UPDATE SET
a.col1 = b.col1,
a.col2 = b.col2,
a.col3 = 13
WHERE a.col3 = 6 and
b.col4 ='N'
WHEN NOT MATCHED
THEN
INSERT (a.col1, a.col2, a.col3)
VALUES (b.col1, b.col3, 1)
where b.col4 = 'N'
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
-
5
-
2
13 Comments
how can we use them separately, I mean using a seperate update statement. If so then it won't work for me because it has to update only when both conditions are met.
Try this please and let me know the result:
MERGE INTO table a
USING table b
ON (b.id = a.id)
WHEN MATCHED
THEN
UPDATE SET
a.col1 = b.col1,
a.col2 = b.col2,
a.col3 = 13
WHERE a.col3 = 6 and
b.col4 ='N'
WHEN NOT MATCHED
THEN
update set
a.col1 = b.col1,
a.col2 = b.col3,
a.col3 = 1
where b.col4 = 'N'
i believe i might of rushed abit into answering that one!
MERGE INTO table a
USING table b
ON (b.id = a.id)
WHEN MATCHED
THEN
UPDATE SET
a.col1 = b.col1,
a.col2 = b.col2,
a.col3 = 13
WHERE a.col3 = 6 and
b.col4 ='N'
WHEN NOT MATCHED
THEN
INSERT into a (a.col1, a.col2, a.col3)
VALUES (b.col1, b.col3, 1)
where b.col4 = 'N'
MERGE INTO table a
USING table b
ON (b.id = a.id)
WHEN MATCHED
THEN
UPDATE SET
a.col1 = b.col1,
a.col2 = b.col2,
a.col3 = 13
WHERE a.col3 = 6 and
b.col4 ='N'
WHEN NOT MATCHED
THEN
INSERT into a (a.col1, a.col2, a.col3)
VALUES (b.col1, b.col3, 1)
where b.col4 = 'N'
Hi,
It is saying oracle error ORA-))90% missing keyword near the second update statement.
But when not matched it should insert because it is a new record.
It is saying oracle error ORA-))90% missing keyword near the second update statement.
But when not matched it should insert because it is a new record.
MERGE INTO table a
USING table b
ON (b.id = a.id)
WHEN MATCHED
THEN
UPDATE SET
a.col1 = b.col1,
a.col2 = b.col2,
a.col3 = 13
WHERE a.col3 = 6 AND
b.col4 ='N'
WHEN NOT MATCHED
THEN
INSERT VALUES (b.col1, b.col2, b.col3)
WHERE b.col4 = 'N'
USING table b
ON (b.id = a.id)
WHEN MATCHED
THEN
UPDATE SET
a.col1 = b.col1,
a.col2 = b.col2,
a.col3 = 13
WHERE a.col3 = 6 AND
b.col4 ='N'
WHEN NOT MATCHED
THEN
INSERT VALUES (b.col1, b.col2, b.col3)
WHERE b.col4 = 'N'
Insert is working fine, the problem is with the update conditions
WHERE a.col3 = 6 AND
b.col4 ='N'
Although there are records to be updated it is not updating.
WHERE a.col3 = 6 AND
b.col4 ='N'
Although there are records to be updated it is not updating.
it will only update the columns where the col3 = 6 and col4 = 'N' i hope you're aware about that.
I'm working blind here and i don't have two tables to work with right now. I'll get home soon and get back to you as soon as i can.
Hopefully you will find the answer meanwhile.
I'm working blind here and i don't have two tables to work with right now. I'll get home soon and get back to you as soon as i can.
Hopefully you will find the answer meanwhile.
Featured Post
![[Webinar] How Hackers Steal Your Credentials](https://filedb.experts-exchange.com/files/public/2017/5/20/3315d9d8-8110-4d52-be94-a5aacdd83e4b.png)
Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here.
General principle of Oracle compression
Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month6 days, 8 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.