asked on
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'
Oracle Database
Last Comment
awking00
Use both conditions separately. You should get the same result!
ASKER
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'
ASKER
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.
ASKER
sorry ORA -00905
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'
ASKER
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.
if possible please provide some data to work with. Thanks.
See attached.
merge.txt
merge.txt
ASKER
I tried the above one and it is saying missing keyword at and condition.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.