CODEWITHSUNDEEP
oracle-database
Robouste
Robouste

Reputation: 3680

Oracle - Copy column to another table with condition

I'm trying to copy one column from TABLE A to TABLE B. I saw some solution online but none of them shows how to add a condition.

TABLE A:

TABLE_A_ID
COLUMN_TO_COPY


TABLE B:

TABLE_B_ID
TABLE_A_FK
NEW_COLUMN

I want to copy COLUMN_TO_COPY to NEW_COLUMN but where TABLE_A_ID = TABLE_A_FK

I tried this:

INSERT INTO TABLE_B (NEW_COLUMN )
    SELECT COLUMN_TO_COPY 
    FROM TABLE_A
    WHERE TABLE_A_ID = TABLE_A_FK

But the query doesn't know what TABLE_A_FK is.

Any solution ?

Upvotes: 0

Views: 2052

Answers (2)

Popeye
Popeye

Reputation: 35900

Another way of achieving the desired result is to use the MERGE statement as follows:

MERGE INTO TABLE_B B 
USING ( SELECT * FROM TABLE_A) A 
ON ( A.TABLE_A_ID = B.TABLE_A_FK )
WHEN MATCHED THEN 
UPDATE SET B.NEW_COLUMN = A.COLUMN_TO_COPY;

Cheers!!

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You want an update here, not an insert:

UPDATE TABLE_B b
SET NEW_COLUMN = (SELECT COLUMN_TO_COPY FROM TABLE_A a WHERE a.TABLE_A_ID = b.TABLE_A_FK);

If you only want to make an update where an actual match happens between the two tables, then add an exists clause:

UPDATE TABLE_B b
SET NEW_COLUMN = (SELECT COLUMN_TO_COPY FROM TABLE_A a WHERE a.TABLE_A_ID = b.TABLE_A_FK)
WHERE EXISTS (SELECT 1 FROM TABLE_A a WHERE a.TABLE_A_ID = b.TABLE_A_FK);

Upvotes: 3

Related Questions