Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
8 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
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Oracle mutateing errors | 3 | 23 | |
| join actual table rows based on the column | 25 | 42 | |
| setting local variables in a cursor block | 3 | 41 | |
| oracle spooling query into csv eliminating new line character | 9 | 53 |
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax:
(CODE)
This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query
Introduction
If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them. …
This video shows syntax for various backup options while discussing how the different basic backup types work. It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility. The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month8 days, 8 hours left to enroll
738 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.