This question is similar to others, but is specific to Oracle SQL and specific to the setting foreign key scenario, the combination of which comes with some limitations that other SQL dialects don’t.

We are trying to migrate some columns/data from Table A to Table B. These columns already exist in Table B but obviously don’t contain all the rows from Table A. After the migration the columns should be removed from Table A and instead a foreign key should be pointing to Table B.

I have already created the foreign key

ALTER TABLE A
    ADD fk_ID NUMBER(19);

ALTER TABLE A
    ADD FOREIGN KEY (fk_ID)
    REFERENCES B (id);

Now I want to do something like (pseudo-code)

INSERT INTO B (
    apple,
    banana,
    orange,
)
SELECT
    apple,
    banana,
    orange,
FROM A RETURNING ID
SET A.fk_ID = ID;

But obviously this doesn’t work.

I’ve tried googling but I can’t seem to find any working solution. Any ideas?