By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1( refid, CompanyNumber, AddressID ) AS
SELECT 'xyz', 230, CAST(null AS NUMBER) FROM DUAL UNION ALL
SELECT 'yzf', 239, null FROM DUAL;
2 rows affected
CREATE TABLE table2( addressId, company ) AS
SELECT 11223344, 231 FROM DUAL UNION ALL
SELECT 11223345, 230 FROM DUAL UNION ALL
SELECT 11223354, 239 FROM DUAL UNION ALL
SELECT 11223334, 239 FROM DUAL
4 rows affected
MERGE INTO table1 dst
USING (
SELECT t1.rid,
t2.addressID
FROM (
SELECT addressId,
company,
ROW_NUMBER() OVER (
PARTITION BY company ORDER BY DBMS_RANDOM.VALUE()
) AS rn
FROM table2 t2
WHERE NOT EXISTS (
SELECT 1
FROM table1 t1
WHERE t1.companyNumber = t2.company
AND t1.addressId = t2.addressId
)
) t2
INNER JOIN
(
SELECT ROWID AS rid,
companyNumber,
ROW_NUMBER() OVER (
PARTITION BY companyNumber ORDER BY DBMS_RANDOM.VALUE()
) AS rn
FROM table1
WHERE addressId IS NULL
) t1
ON ( t1.companyNumber = t2.company AND t1.rn = t2.rn )
) src
ON ( src.rid = dst.ROWID )
WHEN MATCHED THEN
UPDATE
SET addressId = src.addressId;
2 rows affected
SELECT * FROM table1;
| REFID | COMPANYNUMBER | ADDRESSID |
|---|---|---|
| xyz | 230 | 11223345 |
| yzf | 239 | 11223354 |