Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.
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
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!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why doesn't the Oracle optimizer use my index?
Querying too much data
Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table.
So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to copy a database user from one database to another user DBMS_METADATA. It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month11 days, 8 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.