By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE SEQUENCE your_adr_id_sequence START WITH 11;
CREATE TABLE table_name (adr_id, ver_id, address) AS
SELECT 1, 1, 'newYork' FROM DUAL UNION ALL
SELECT 1, 2, 'newYork' FROM DUAL UNION ALL
SELECT 1, 3, 'newYork' FROM DUAL UNION ALL
SELECT 4, 1, 'Washington' FROM DUAL UNION ALL
SELECT 4, 2, 'Washington' FROM DUAL;
5 rows affected
MERGE INTO table_name dst
USING (
SELECT ROWID AS rid,
COUNT(*) OVER (PARTITION BY adr_id) AS cnt,
ROW_NUMBER() OVER (PARTITION BY adr_id ORDER BY ver_id) AS rn
FROM table_name
) src
ON (src.cnt > 1 AND dst.ROWID = src.rid)
WHEN MATCHED THEN
UPDATE
SET adr_id = YOUR_ADR_ID_SEQUENCE.NEXTVAL,
ver_id = 0
DELETE WHERE rn > 1;
5 rows affected
SELECT * FROM table_name;
| ADR_ID | VER_ID | ADDRESS |
|---|---|---|
| 11 | 0 | newYork |
| 14 | 0 | Washington |