I have a requirement to compare with one table and update another table if there are null records. Table LEGACY_CUST_INFO has correct CUST_PRIVILEGE_NUMBER. So, I need to findout the null records in CUST_PRIVILEGE_NUMBER in second table CUST_INFO and update CUST_PRIVILEGE_NUMBER from first table LEGACY_CUST_INFO. Apart from updating CUST_PRIVILEGE_NUMBER, I will also need to update UPDATED column with SYSDATE and UPDATED_BY with 'ADMIN'. ROW_ID in both the tables match are an exact match.
Below are the two table structures
Table 1 Name : LEGACY_CUST_INFO
ROW_ID CUST_PRIVILEGE_NUMBER UPDATED UPDATED_BY
1-AB 2345 16-JUN-2022 ADMIN
1-AC 3345 17-JUN-2022 ADMIN
1-AV 2335 20-JUN-2022 ADMIN
1-AN 2042 18-JUN-2022 ADMIN
1-AG 2255 01-JUN-2022 ADMIN
Table 2 Name : CUST_INFO
ROW_ID CUST_PRIVILEGE_NUMBER UPDATED UPDATED_BY
1-AB NULL 22-JUN-2022 ADMIN
1-AC NULL 22-JUN-2022 ADMIN
1-AV 5409 22-JUN-2022 ADMIN
1-AN NULL 22-JUN-2022 ADMIN
1-AG 8848 22-JUN-2022 ADMIN
It is not updating anything when I use the below update. Is there a way to update the nulls ?
merge into cust_info dst
using (
select lci.row_id, lci.cust_privilege_number
from legacy_cust_info lci
inner join cust_info ci on ci.row_id = lci.row_id
where ci.cust_privilege_number != lci.cust_privilege_number
) src
on (dst.row_id = src.row_id)
when matched then
update set dst.cust_privilege_number = src.cust_privilege_number, dst.updated= sysdate, dst.updated_by = 'ADMIN';
Sample data:
SQL> SELECT * FROM legacy_cust_info;
ROW_ CUST_PRIVILEGE_NUMBER UPDATED UPDAT
---- --------------------- ----------- -----
1-AB 2345 16-JUN-2022 ADMIN
1-AC 3345 17-JUN-2022 ADMIN
1-AV 2335 20-JUN-2022 ADMIN
1-AN 2042 18-JUN-2022 ADMIN
1-AG 2255 01-JUN-2022 ADMIN
SQL> SELECT * FROM cust_info;
ROW_ CUST_PRIVILEGE_NUMBER UPDATED UPDAT
---- --------------------- ----------- -----
1-AB 22-JUN-2022 ADMIN
1-AC 22-JUN-2022 ADMIN
1-AV 5409 22-JUN-2022 ADMIN
1-AN 22-JUN-2022 ADMIN
1-AG 5515 22-JUN-2022 ADMIN
Use IS NULL condition in MERGE's WHERE clause because nothing is "equal" to NULL so this: WHERE d.cust_privilege_number <> s.cust_privilege_number isn't enough.
SQL> MERGE INTO cust_info d
2 USING legacy_cust_info s
3 ON (d.row_id = s.row_id)
4 WHEN MATCHED
5 THEN
6 UPDATE SET
7 d.cust_privilege_number = s.cust_privilege_number,
8 d.updated = SYSDATE,
9 d.updated_by = 'ADMIN'
10 WHERE d.cust_privilege_number <> s.cust_privilege_number
11 OR d.cust_privilege_number IS NULL;
5 rows merged.
Result:
SQL> SELECT * FROM cust_info;
ROW_ CUST_PRIVILEGE_NUMBER UPDATED UPDAT
---- --------------------- ----------- -----
1-AB 2345 21-JUN-2023 ADMIN
1-AC 3345 21-JUN-2023 ADMIN
1-AV 2335 21-JUN-2023 ADMIN
1-AN 2042 21-JUN-2023 ADMIN
1-AG 2255 21-JUN-2023 ADMIN
SQL>