By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE Status (Id, StatusCode, Description) AS
SELECT 11, 'CO', 'Completed' FROM DUAL UNION ALL
SELECT 22, 'CA', 'Carried' FROM DUAL UNION ALL
SELECT 33, 'AA', 'Carried' FROM DUAL;
3 rows affected
CREATE TABLE MainTable (Id NUMBER, StatusTableId NUMBER, MainTableData VARCHAR2(10));
INSERT INTO maintable (id, maintabledata)
SELECT 1, 'ancsd' FROM DUAL UNION ALL
SELECT 2, 'dadsa' FROM DUAL UNION ALL
SELECT 3, 'adasd' FROM DUAL;
3 rows affected
MERGE INTO maintable m
USING (
SELECT id,
ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM status
) s
ON (m.id = s.rn)
WHEN MATCHED THEN
UPDATE
SET statustableid = s.id;
3 rows affected
SELECT *
FROM maintable;
| ID | STATUSTABLEID | MAINTABLEDATA |
|---|---|---|
| 1 | 11 | ancsd |
| 2 | 22 | dadsa |
| 3 | 33 | adasd |
MERGE INTO maintable m
USING status s
ON (m.id = SUBSTR(s.id, 1, 1))
WHEN MATCHED THEN
UPDATE
SET statustableid = s.id;
3 rows affected
SELECT *
FROM maintable;
| ID | STATUSTABLEID | MAINTABLEDATA |
|---|---|---|
| 1 | 11 | ancsd |
| 2 | 22 | dadsa |
| 3 | 33 | adasd |