1 Reply Latest reply: Mar 10, 2008 12:02 PM by AntsHindpere RSS

    update with subquery

    27105
      I want to update a table with data extracted from a xmltype.
      But Oracle extracts only the first value from the subquery.
      Am I missing something or this is the expected output...

      drop table t

      create table t(id number, val varchar2(20));

      insert into t(id, val) values (1, null)

      insert into t(id, val) values (2, null)

      select *
      from t

      ID     VAL
      1     
      2     

      update t set(val) =
      (select t.id from dual)
      where id = t.id

      select *
      from t

      ID     VAL
      1     1
      2     2

      update t set(val) =
      (select extractValue(
                     extract(xmltype('<root>'||
                                         '<node id="1">'||
                                         '<value>1</value>'||
                                         '</node>'||
                                         '<node id="2">'||
                                         '<value>2</value>'||
                                         '</node>'||
                                         '</root>'),
                               '//root/node[@id='||to_char(t.id)||']'),
                     '/node/value') from dual)
      where id = t.id

      select *
      from t

      ID     VAL
      1     1
      2     1 -- <-- should be 2!
        • 1. Re: update with subquery
          AntsHindpere
          Hi,

          try this way:
          SQL> CREATE TABLE tab AS SELECT 1 id,cast(NULL AS VARCHAR2(20)) val FROM dual UNION ALL
            2                      SELECT 2,NULL FROM dual;
          
          Table created.
          
          SQL> DESC tab;
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           ID                                                 NUMBER
           VAL                                                VARCHAR2(20)
          
          SQL> 
          SQL> SELECT * FROM tab;
          
                  ID VAL                                                                  
          ---------- --------------------                                                 
                   1                                                                      
                   2                                                                      
          
          SQL> 
          SQL> UPDATE tab t
            2  SET val=(
            3      SELECT
            4         extractValue(
            5            extract(
            6               XMLType('<root>
            7                          <node id="1">
            8                            <value>1</value>
            9                          </node>
           10                          <node id="2">
           11                            <value>2</value>
           12                          </node>
           13                        </root>')
           14               ,'/root/node[@id='||id||']')
           15            ,'/node/value') va
           16      FROM tab s
           17    WHERE t.id=s.id) ;
          
          2 rows updated.
          
          SQL> SELECT * FROM tab;
          
                  ID VAL                                                                  
          ---------- --------------------                                                 
                   1 1                                                                    
                   2 2                                                                    
          
          SQL> spool off;
          btw, you don't need to || every line in xml, and you don't need to do to_char() when using ||

          Ants