Hello Experts,
Looking for suggestion idea to perform below “As is” to “To be” situation.
Here: Table A has below columns
e.g.
- store 10, cust no 256 has a child 1 which has card GOLD of version 3 and previous card are blocked.
- expectation is to have one entry with card version max -1 and blocked should be No
- To be:
- store 10, cust no 256 of child 1 and card version max -1 => 2 and blocked “Yes”
Note: update is not allowed, only delete and re-insert with max version -1
DB we are using 19c, is there any best way to perform this activity.
<table><tbody><tr><td style="height:15.75pt;width:54pt;">AS IS</td><td style="width:54pt;"> </td><td style="width:54pt;"> </td><td style="width:54pt;"> </td><td style="width:60pt;"> </td><td style="width:41pt;"> </td></tr><tr><td style="height:15.75pt;">Store</td><td style="border-left-style:none;">CUST</td><td style="border-left-style:none;">child</td><td style="border-left-style:none;">card</td><td style="border-left-style:none;">card version</td><td style="border-left-style:none;">blocked</td></tr><tr><td style="height:15.0pt;text-align:right;">10</td><td style="border-left-style:none;text-align:right;">256</td><td style="border-left-style:none;text-align:right;">1</td><td style="border-left-style:none;">GOLD</td><td style="border-left-style:none;text-align:right;">1</td><td style="border-left-style:none;">Yes</td></tr><tr><td style="border-top-style:none;height:15.0pt;text-align:right;">10</td><td style="border-left-style:none;border-top-style:none;text-align:right;">256</td><td style="border-left-style:none;border-top-style:none;text-align:right;">1</td><td style="border-left-style:none;border-top-style:none;">GOLD</td><td style="border-left-style:none;border-top-style:none;text-align:right;">2</td><td style="border-left-style:none;border-top-style:none;">Yes</td></tr><tr><td style="border-top-style:none;height:15.0pt;text-align:right;">10</td><td style="border-left-style:none;border-top-style:none;text-align:right;">256</td><td style="border-left-style:none;border-top-style:none;text-align:right;">1</td><td style="border-left-style:none;border-top-style:none;">GOLD</td><td style="border-left-style:none;border-top-style:none;text-align:right;">3</td><td style="border-left-style:none;border-top-style:none;">No</td></tr><tr><td style="height:15.0pt;"> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr><td style="height:15.75pt;">To Be</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr><td style="height:15.75pt;">Store</td><td style="border-left-style:none;">cust</td><td style="border-left-style:none;">child</td><td style="border-left-style:none;">card</td><td style="border-left-style:none;">card version</td><td style="border-left-style:none;">blocked</td></tr><tr><td style="height:15.0pt;text-align:right;">10</td><td style="border-left-style:none;text-align:right;">256</td><td style="border-left-style:none;text-align:right;">1</td><td style="border-left-style:none;">GOLD</td><td style="border-left-style:none;text-align:right;">2</td><td style="border-left-style:none;">No</td></tr></tbody></table>