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.