By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1(id, name, gender, age) AS
SELECT 134, 'Rohit', 'M', 22 FROM DUAL UNION ALL
SELECT 214, 'Ayush', 'M', 21 FROM DUAL UNION ALL
SELECT 221, 'Nikhil', 'M', 24 FROM DUAL UNION ALL
SELECT 112, 'Astha', 'F', 23 FROM DUAL;
4 rows affected
CREATE TABLE table2(id, name, gender, age, phone_num) AS
SELECT 134, 'Rohit', 'M', 22, CAST('1232' AS VARCHAR2(20)) FROM DUAL UNION ALL
SELECT 215, 'Ankit', 'M', 21, '1234' FROM DUAL UNION ALL
SELECT 221, 'Nikhil', 'M', 24, '1232' FROM DUAL UNION ALL
SELECT 112, 'Abced', 'F', 23, '123223' FROM DUAL;
4 rows affected
CREATE TABLE table3(id, phone_num) AS
SELECT 134, '234234' FROM DUAL UNION ALL
SELECT 214, '1234' FROM DUAL UNION ALL
SELECT 221, '1234234' FROM DUAL UNION ALL
SELECT 112, '1234532' FROM DUAL;
4 rows affected
MERGE INTO table2 dst
USING (
SELECT t1.*, t3.phone_num
FROM table1 t1
LEFT OUTER JOIN table3 t3
ON (t1.id = t3.id)
) src
ON (src.id = dst.id)
WHEN NOT MATCHED THEN
INSERT (id, name, gender, age, phone_num)
VALUES (src.id, src.name, src.gender, src.age, src.phone_num);
1 rows affected
SELECT * FROM table2;
| ID | NAME | GENDER | AGE | PHONE_NUM |
|---|---|---|---|---|
| 134 | Rohit | M | 22 | 1232 |
| 215 | Ankit | M | 21 | 1234 |
| 221 | Nikhil | M | 24 | 1232 |
| 112 | Abced | F | 23 | 123223 |
| 214 | Ayush | M | 21 | 1234 |
MERGE INTO table2 dst
USING (
SELECT t1.*, t3.phone_num
FROM table1 t1
LEFT OUTER JOIN table3 t3
ON (t1.id = t3.id)
) src
ON (src.id = dst.id)
WHEN MATCHED THEN
UPDATE
SET name = src.name,
gender = src.gender,
age = src.age,
phone_num = src.phone_num
WHEN NOT MATCHED THEN
INSERT (id, name, gender, age, phone_num)
VALUES (src.id, src.name, src.gender, src.age, src.phone_num);
4 rows affected
SELECT * FROM table2;
| ID | NAME | GENDER | AGE | PHONE_NUM |
|---|---|---|---|---|
| 134 | Rohit | M | 22 | 234234 |
| 215 | Ankit | M | 21 | 1234 |
| 221 | Nikhil | M | 24 | 1234234 |
| 112 | Astha | F | 23 | 1234532 |
| 214 | Ayush | M | 21 | 1234 |