Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Help with a Merge statement in oracle
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'
Who is Participating?
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month3 days, 21 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
merge.txt