Home » SQL & PL/SQL » SQL & PL/SQL » how to update hierarchical query (11g,toad 12.1)
how to update hierarchical query [message #612847] Sat, 26 April 2014 05:09 Go to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
how to update hierarchical query only when leaf=1

UPDATE CUSTOMER SET CLASS='SHIP-TO' (when the leaf=1)

Report message to a moderator

Re: how to update hierarchical query [message #612848 is a reply to message #612847] Sat, 26 April 2014 05:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Use MERGE:

MERGE
  INTO CUSTOMER D
  USING (
         SELECT  ROWID RID
           FROM  CUSTOMER
           WHERE CONNECT_BY_ISLEAF = 1
           START WITH ...
           CONNECT BY ...
        ) S
  ONE D.ROWID = S.RID
  WHEN MATCHED
    THEN
      UPDATE
         SET D.CLASS='SHIP-TO'
/


SY.

Report message to a moderator

Re: how to update hierarchical query [message #612853 is a reply to message #612848] Sat, 26 April 2014 08:06 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
can i use something like this

UPDATE CUSTOMER SET CLASS='SHIP-TO'
WHERE connect_by_isleaf=1
start with cust_id=12660
CONNECT BY PRIOR cust_id = cust_cust_id;

Report message to a moderator

Re: how to update hierarchical query [message #612855 is a reply to message #612853] Sat, 26 April 2014 08:18 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
No. But you can use:

UPDATE CUSTOMER
   SET CLASS='SHIP-TO' 
 WHERE ROWID IN (
                 SELECT  ROWID
                   FROM  CUSTOMER
                   WHERE CONNECT_BY_ISLEAF = 1
                   START WITH CUST_ID = 12660
                   CONNECT BY PRIOR CUST_ID = CUST_CUST_ID
                )
/


SY.

Report message to a moderator

Previous Topic: Nested table
Next Topic: Cannot refresh Materialized View with Join in Oracle 12c 12.1.0.1.0 after Create
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 20 05:18:56 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.