This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 22, 2013 3:03 AM by Nitesh. RSS

Need a solution for deleting values in table

Nitesh. Explorer
Currently Being Moderated
Oracle version details

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production



Table name nit_xml

Structure

TABLE nit_xml
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_DTLS XMLTYPE

Cust_dtls XML Data's

<H>
<cust_dtlss>
<cust_name>Nitesh</cust_name>
<cust_phone>1234567890</cust_phone>
<cust_place>Ambattur</cust_place>
</cust_dtlss>
<cust_dtlss>
<cust_name>Bharathi</cust_name>
<cust_phone>123</cust_phone>
<cust_place>Mylapore</cust_place>
</cust_dtlss>
</H>


I want to remove Bharathi alone from this data's and i have tried this way

DELETE FROM nit_xml a ,
TABLE (XMLSEQUENCE (EXTRACT (a.cust_dtls, '/H/cust_dtlss'))) b;
WHERE TRIM(EXTRACTVALUE (value(b), '/H/cust_dtlss/cust_name/text()')) = 'Bharathi';


but i am unable to make it so can anyone suggest any best way ..

Rgrds,
Nitkhush...
  • 1. Re: Need a solution for deleting values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Try this
    update nit_xml
       set cust_dtls = deletexml(cust_dtls, '/H/cust_dtlss/cust_name[text()="Bharathi"]');
    or if you want to delete the entire node cust_dtlss for cust_name = "Bharathi" you can try
    update nit_xml
       set cust_dtls = deletexml(cust_dtls, '/H/cust_dtlss[cust_name/text()="Bharathi"]');
  • 2. Re: Need a solution for deleting values in table
    Nitesh. Explorer
    Currently Being Moderated
    Hey thanx a lot but what if i want to delete entire that particular row itself means because update cannot be used in that case know and i am not having any other column which could have helped to delete that particular row alone and delete statement cannot help ah for XML as through update only we have to remove data's huh which are present in nodes ..
  • 3. Re: Need a solution for deleting values in table
    Nitesh. Explorer
    Currently Being Moderated
    Sorry i got the solution for deleting data's for entire node also but i am unable to delete that row as it shows oraXML though data's are NULL inside it so how to do that ..
  • 4. Re: Need a solution for deleting values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Niteshkhush wrote:
    Sorry i got the solution for deleting data's for entire node also but i am unable to delete that row as it shows oraXML though data's are NULL inside it so how to do that ..
    You can use EXISTSNODE to check if a node exist and then DELETE the entire row.
    delete
      from nit_xml
     where existsnode(cust_dtls, '/H/cust_dtlss/cust_name[text()="Bharathi"]') = 1
  • 5. Re: Need a solution for deleting values in table
    Nitesh. Explorer
    Currently Being Moderated
    No i tried in this way but it didnt work out



    UPDATE nit_xml
    SET cust_dtls =
    DELETEXML (cust_dtls,
    '/H/cust_dtlss[cust_phone/text()="123"]'
    )
    WHERE EXISTSNODE (cust_dtls, '/H/cust_dtlss/cust_name[text()="Bharathi"]') =
    1;


    Anyways your provided solution is really working but why we are giving value as 1 here i mean the value Bharathi occurs in more than one row and xmlpath is also same but when i am trying to give value as 2 to delete 2 rows at a time then its not working actually why so..

    DELETE FROM nit_xml
    WHERE EXISTSNODE (cust_dtls,
    '/H/cust_dtlss/cust_name[text()="Bharathi"]'
    ) = 1;

    Thanks,
    Nitkhush.
  • 6. Re: Need a solution for deleting values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Anyways your provided solution is really working but why we are giving value as 1 here i mean the value Bharathi occurs in more than one row and xmlpath is also same but when i am trying to give value as 2 to delete 2 rows at a time then its not working actually why so..

    DELETE FROM nit_xml
    WHERE EXISTSNODE (cust_dtls,
    '/H/cust_dtlss/cust_name[text()="Bharathi"]'
    ) = 1;

    Thanks,
    Nitkhush.
    How about reading the fine document on [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions048.htm#SQLRF00637]EXISTSNODE. Look at the return value.
  • 7. Re: Need a solution for deleting values in table
    Nitesh. Explorer
    Currently Being Moderated
    Yes karthick I have seen that document already it says as return value is either o or 1 as if exists then it will delete when we give value as 1 and if we give 0 then even though data's exists but it will remove unmatched one's only and i am having same data's in 2 rows and XMLPATH is also same bt its deleting only one row so i just thought as is thr any values available rather than 0 or 1 to delete multiple rows at a same time..

    Have a look on data's once .. The below mentioned data is available in 2 rows .. But when i tried through update then it didnt update as update just modifies but dont delete XMLtype row fully where as as u provided delete which helped but its deleting single row alone not other duplicates.. I tried also .. Is thr any other way coz 2 rows exists then why its deleting single row alone



    <H>
    <cust_dtlss>
    <cust_name>Nitesh</cust_name>
    <cust_phone>1234567890</cust_phone>
    <cust_place>Ambattur</cust_place>
    </cust_dtlss>
    <cust_dtlss>
    <cust_name>Bharathi</cust_name>
    <cust_phone>123</cust_phone>
    <cust_place>Mylapore</cust_place>
    </cust_dtlss>
    </H>


    Thanks,
    Nitesh..

    Edited by: Niteshkhush on Feb 22, 2013 3:00 PM
  • 8. Re: Need a solution for deleting values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Niteshkhush wrote:
    Yes karthick I have seen that document already it says as return value is either o or 1 as if exists then it will delete when we give value as 1 and if we give 0 then even though data's exists but it will remove unmatched one's only and i am having same data's in 2 rows and XMLPATH is also same bt its deleting only one row so i just thought as is thr any values available rather than 0 or 1 to delete multiple rows at a same time..

    Have a look on data's once .. The below mentioned data is available in 2 rows .. But when i tried through update then it didnt update as update just modifies but dont delete XMLtype row fully where as as u provided delete which helped but its deleting single row alone not other duplicates.. I tried also .. Is thr any other way coz 2 rows exists then why its deleting single row alone



    <H>
    <cust_dtlss>
    <cust_name>Nitesh</cust_name>
    <cust_phone>1234567890</cust_phone>
    <cust_place>Ambattur</cust_place>
    </cust_dtlss>
    <cust_dtlss>
    <cust_name>Bharathi</cust_name>
    <cust_phone>123</cust_phone>
    <cust_place>Mylapore</cust_place>
    </cust_dtlss>
    </H>


    Thanks,
    Nitesh..
    Let me be clear. Based on your example you have only 1 row in your table nit_xml. This single row has a XMLType which has 2 cust_dtlss node.

    Your objective is to delete the Row of a table which has a XMLType having cust_name = Bharathi

    And the DELETE statement does it, It delets the entire row, So whats the problem?
  • 9. Re: Need a solution for deleting values in table
    Nitesh. Explorer
    Currently Being Moderated
    But i have inserted one more row so only i mentioned clearly as same data's exists in 2 rows but its deleting only one row dude .. Its yes at first i shown 1 row only but in my last post i mentioned as duplicate exists so its not working so Is there any other way to do ...
  • 10. Re: Need a solution for deleting values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Niteshkhush wrote:
    But i have inserted one more row so only i mentioned clearly as same data's exists in 2 rows but its deleting only one row dude .. Its yes at first i shown 1 row only but in my last post i mentioned as duplicate exists so its not working so Is there any other way to do ...
    Works for me
    SQL> select * from nit_xml;
     
    CUST_DTLS
    --------------------------------------------------------------------------------
    <H>
    <cust_dtlss>
    <cust_name>Nitesh</cust_name>
    <cust_phone>1234567890</cust_phone>
    <cust_place>Ambattur</cust_place>
    </cust_dtlss>
    <cust_dtlss>
    <cust_name>Bharathi</cust_name>
    <cust_phone>123</cust_phone>
    <cust_place>Mylapore</cust_place>
    </cust_dtlss>
    </H>
     
    <H>
    <cust_dtlss>
    <cust_name>Nitesh</cust_name>
    <cust_phone>1234567890</cust_phone>
    <cust_place>Ambattur</cust_place>
    </cust_dtlss>
    <cust_dtlss>
    <cust_name>Bharathi</cust_name>
    <cust_phone>123</cust_phone>
    <cust_place>Mylapore</cust_place>
    </cust_dtlss>
    </H>
     
     
    SQL> delete
      2    from nit_xml
      3   where existsnode(cust_dtls, '/H/cust_dtlss/cust_name[text()="Bharathi"]') = 1
      4  /
     
    2 rows deleted.
     
    SQL> select * from nit_xml;
     
    no rows selected
     
    SQL> 
  • 11. Re: Need a solution for deleting values in table
    Nitesh. Explorer
    Currently Being Moderated
    Hey Sorrie I made some mistake in XMLPATH Sorrie ..

    Thankss a lot ..

    Rgds,

    Nitkhush ...
  • 12. Re: Need a solution for deleting values in table
    odie_63 Guru
    Currently Being Moderated
    EXISTSNODE is deprecated.

    Use XMLExists instead, as already shown in {message:id=10864233}
  • 13. Re: Need a solution for deleting values in table
    Nitesh. Explorer
    Currently Being Moderated
    But XMLEXISTS have shown here some errors when i tried yesterday and i even told u as got some errors .. Even i am unable to understand it clearly as EXISTSNODE i have used already so i have some idea but XMLEXISTS unable to get it and here thrown errors too Odie..

    Anyways Thank you...

    Rgds,
    Nitesh pareek.
  • 14. Re: Need a solution for deleting values in table
    odie_63 Guru
    Currently Being Moderated
    But XMLEXISTS have shown here some errors when i tried yesterday and i even told u as got some errors
    You sound like an average end-user complaining that "something" doesn't work "somewhere".
    You're a developer, you even say "Software engineer" in your profile. Aren't you able to give what specific errors you had on what statements?

    Post the query you've tried with XMLEXISTS, along with the error stack, then I'll be able to show you where you've gone wrong and explain. Thanks.
    .. Even i am unable to understand it clearly as EXISTSNODE i have used already so i have some idea but XMLEXISTS unable to get it and here thrown errors too Odie..
    Apparently you didn't know any of those two functions till yesterday, so instead of learning how to use EXISTSNODE which again is deprecated, why not learning how to use up-to-date features?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points