Oracle 11g, delete a specific product ID from the Watchlists column of the Member Table-CodePudding

The table below is a Member Table.

 ---------- ----------------- 
| memberId |   watchlists    |
 ---------- ----------------- 
| userId1  | 1,30,2,40,9,19, |
| userId2  | 9,99,999,       |
 ---------- -----------------

and this is the Product Table.

 ----------- ------------- 
| productId | productName |
 ----------- ------------- 
| 1         | tv          |
| 2         | laptop      |
| 9         | mouse       |
| 19        | phone       |
 ----------- ------------- 
... and more products

The watchlists column of the Member Table stores the IDs of certain products separated by commas.

If a specific object is deleted from the Product Table, it must be deleted from the watchlists column of the Member Table because the product no longer exists on the Product Table.

The way I tried:

I was simply trying to use the update statement in the Java DAO class as follows. But there is a problem.



    update Member 
    set watchlists = replace(Member.watchlists, '9,', '');

If I try to delete an product with an ID of '9' , what I wanted was only '9,' should be deleted (replaced by a space ''). However, the IDs of other products that contain '9' are also affected.

The result I want:

 ---------- --------------- 
| memberId |  watchlists   |
 ---------- --------------- 
| userId1  | 1,30,2,40,19, |
| userId2  | 99,999,       |
 ---------- ---------------

Only one item with product ID '9' is deleted.

Result when executing the update statement written above: (result that is different from what I expected.)

 ---------- ------------- 
| memberId | watchlists  |
 ---------- ------------- 
| userId1  | 1,30,2,40,1 |
| userId2  | 999         |
 ---------- -------------

I'm not sure how to solve it. It doesn't matter if you use a trigger, or solve it in a DAO class in Java.

CodePudding user response：