Oracle 23c

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