Multivalued for SQL LIKE/CONTAINS using a query or similar

Question

I have a oracle DB with the following columns:

  ID   / ARTICLE_ID / USER_ID   / PARENT  / HIERARCHY   
  100  / 41513151   / 99        / 0       / 100
  200  / 12151351   / 101       / 100     / 100|200
  300  / 12414144   / 130       / 200     / 100|200|300

This represent a comment (id 100) of the user 99 with a reply of the user 101 to his comment and at the same time this last comment has a reply of the user 130. Graphically.

Hello im user 99 --->Hello im user 101 and this is my reply to the user 99 --------Hello im user 130 and this is my reply to the user 101

So in my db the comment with id 300 his parent is the comment 200 and the last one his parent is the comment 100. HIERARCHY (100 <- 200 <- 300).

I did the following query for delete the comments and subcomments after an user is deleted.

  DELETE FROM TABLE_NAME
        WHERE (USER_ID = ? AND STATUS IN (1,2)) OR ID in (SELECT A.ID 
        FROM "TABLE_NAME A INNER JOIN TABLE_NAME B 
        ON A.PARENT = B.ID 
        WHERE A.STATUS IN (1,2) AND B.USER_ID=?)

This query works when for each comment just there are a reply, but it exist N replies for each comment (my case), this dont work.

I would like use HIERARCHY for to know that comments are related to a comment of an user deleted taking in account the last idea.

If i build a query as: SELECT ID FROM TABLE_NAME WHERE HIERARCHY LIKE '%X%'

If X was values of the a query or something will be worth, but i think that my battle is lost but something similar is not possible or almost impossible. Am i wrong?.

Cheers

Comma (or whatever) separated values in a column is never easy... (I'd consider redesigning the tables if I had those.) — jarlh, Dec 16 '15 at 15:28
You have to transform that `|` separated string into rows. The you execute your delete with it. There already answers to this operation here, take a look: http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle — Jorge Campos, Dec 16 '15 at 15:41
@jmhdez Databases have many ways to help support millions, or even trillions of rows. Databases can *not* easily support data that is not in first normal form. If values in a database cannot be easily joined they might as well be stored in a text file. — Jon Heller, Dec 17 '15 at 03:20

dcieslak · Accepted Answer · 2015-12-19T13:00:55.767

1

Did you try recursive query without using hierarchy at all? I think it supposed to be very fast.

  delete  TABLE_NAME where id  in (
   with tree ( child)
   as 
   ( select 
            id          
     from  TABLE_NAME where user_id = 99
    union all
     select  
       c.id
     from  tree t 
      join TABLE_NAME c 
      on c.parent = t.child )
   select child from tree 
  )

I added SQL Fiddle sample

edited Dec 19 '15 at 13:00

answered Dec 17 '15 at 09:04

dcieslak

2,697
1
12
19

@jmhdez I added sample in SQL Fiddle – dcieslak Dec 19 '15 at 13:01
replace ( 101 , 41513151 , 101 , 0 , '100'); by ( 101 , 41513151 , 101 , 100 , '100|101') or ( 101 , 41513151 , 101 , 0 , '101'); // The first one is not a valid row. – jmhdez Dec 21 '15 at 11:21
so comment with id 200 and id 300 must be in the result. I just see comment id 101. – jmhdez Dec 21 '15 at 11:24
@jmhdez the delete removes 100, 200, 300 and leaves there only 101. Don't you want delete 200 and 300? – dcieslak Dec 21 '15 at 11:58
yes you are right, so fixing the row that i commented it works fine. Thanks! – jmhdez Dec 22 '15 at 12:10

Multivalued for SQL LIKE/CONTAINS using a query or similar

1 Answers1