I have a table like the following one. Parent and child fieldS are in a one (parent) to many (child) relation except for FRANCE. FRANCE has got two parents: EMEA and APAC. What I need is to keep only one relation for FRANCE (the one with the highest customers count) and put the others into a log table.
Please could you help? Many thanks in advance.
Alberto
Original table +-------+--------+--------+-----------------+ | RowID | parent | child | customers count | +-------+--------+--------+-----------------+ | 1 | EMEA | FRANCE | 5 | | 2 | EMEA | ITALY | 2 | | 3 | AMER | USA | 1 | | 4 | AMER | BRASIL | 5 | | 5 | APAC | FRANCE | 1 | | 6 | APAC | JAPAN | 3 | +-------+--------+--------+-----------------+
the final result should be:
Master data table
+-------+--------+--------+-----------------+ | RowID | parent | child | customers count | +-------+--------+--------+-----------------+ | 1 | EMEA | FRANCE | 5 | | 2 | EMEA | ITALY | 2 | | 3 | AMER | USA | 1 | | 4 | AMER | BRASIL | 5 | | 6 | APAC | JAPAN | 3 | +-------+--------+--------+-----------------+
log table
+-------+--------+--------+-----------------+ | RowID | parent | child | customers count | +-------+--------+--------+-----------------+ | 5 | APAC | FRANCE | 1 | +-------+--------+--------+-----------------+
