Copy data from one table to another based on an enum in first table

Viewed 23

I am migrating data from table B to table A & planning to delete table B later in Oracle.

Table A has fields id(primary key), title, description.

Table B has fields id(primary key), parent_id(foreign key(id from table A)), info, type(int, can be 1 OR 2 only). Table B will have two rows for each parent_id, one having type = 1 and another with type = 2.

Now, I want to introduce 2 new columns info1 & info2to table A & fill them with respective data depending upon type enum.

Current approach:

alter table A add info1 VARCHAR2(32); 
alter table A add info2 VARCHAR2(32);

update A 
set 
info1 = (select info from B where type = 1 and A.id = B.parent_id),
info2 = (select info from B where type = 2 and A.id = B.parent_id);

What could be a efficient way to do this when we are doing it for large data.

Thanks in advance.

0 Answers
Related