I have noticed WHERE clause can be used in 2 different ways in a MERGE statement.
In the below example, I'm trying to update the REGION_INFO field in TEST1 table from TEST2 using field: FMLY_KEY.
Though both statements give the same result in this example, is there a recommended way to use WHERE clause in MERGE statement? or Are both ways of using the WHERE clause works the same in any scenario?
CREATE TABLE test1 (enty_key NUMBER, fmly_key NUMBER, region VARCHAR2(4), region_info varchar2(100));
CREATE TABLE test2 (fmly_key NUMBER, fmly_name VARCHAR(20));
INSERT INTO test1(enty_key, fmly_key, region) VALUES (1, 100, 'APAC');
INSERT INTO test1(enty_key, fmly_key, region) VALUES (2, 100, 'EMEA');
INSERT INTO test1(enty_key, fmly_key, region) VALUES (3, 200, 'LTAM');
INSERT INTO test1(enty_key, fmly_key, region) VALUES (4, 200, 'APAC');
INSERT INTO test1(enty_key, fmly_key, region) VALUES (5, 200, 'NMEA');
INSERT INTO test1(enty_key, fmly_key, region) VALUES (6, 300, 'NMEA');
INSERT INTO test1(enty_key, fmly_key, region) VALUES (7, 400, 'APAC');
INSERT INTO test1(enty_key, fmly_key, region) VALUES (8, 400, 'LTAM');
INSERT INTO test2 VALUES (200, 'fmly2');
INSERT INTO test2 VALUES (400, 'fmly4');
MERGE INTO test1 t1
USING test2 t2
ON (t1.fmly_key = t2.fmly_key)
WHEN MATCHED THEN UPDATE
SET t1.region_info = t2.fmly_name||' - '||t1.region
WHERE t1.region = 'APAC';
MERGE INTO test1 t1
USING test2 t2
ON (t1.fmly_key = t2.fmly_key
and t1.region = 'APAC')
WHEN MATCHED THEN UPDATE
SET t1.region_info = t2.fmly_name||' - '||t1.region;
Working on Win 7 Enterprise
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Thanks.