SQL - Split container row conditionally

user_12797449 0
Viewed 49

In the process of migrating containers, if we have two tables;

  1. TABLE_MAPPING (old_value, new_value)
  2. TABLE_USING (value, data...)

TABLE_USING is referencing (FK) a container in a irrelevant table. TABLE_MAPPING is temporarily used for a migration, the goal is to move contents from deprecated to new containers.

The problem here is that sometimes the container is not only replaced, but split into multiple new containers, for example TABLE_MAPPING could contain:

OLD_VALUE NEW VALUE
1 10
1 11
2 20

And the query would result in an "update" of one row with value '1' to two rows with values '10' and '11'.

Is there a plain SQL way to do that? Or should I use PL/SQL?

EDIT: as requested, here is an example of before/after using the TABLE_MAPPING above

Before:

VALUE IRRELEVANT_COLUMNS ...
1 ...
2 ...

After:

VALUE IRRELEVANT_COLUMNS ...
10 ...
12 ...
20 ...
oracle sql
1 Answers

You need two steps. Below I first insert all new rows, then I delete all old rows.

-- insert rows with new values
insert into table_using (value, data ...)
select m.new_value, u.data ...
from table_using u
join table_mapping m on m.old_value = u.value;

-- delete rows with old values
delete from table_using where value in (select old_value from table_mapping);

-- commit the transaction
commit;
user_22707620
Related