Link to home
Create AccountLog in
Avatar of chaituu
chaituuFlag for India

asked on

how to update the table where results are coming from select statement

How to update GT_ADD_ISBNS table if CATEGORY_EXISTS=0 then error_mesage='category not found' , if FINAL_DOCUMENTS is not null then
finalename=FINAL_DOCUMENTS, and FINAL_DOCUMENTS is null then error_mesage='documents not found' ;

SELECT  (SELECT COUNT(*)
		FROM 
		TEMP_NODE_PROPERTIES ANP,
    TEMP_NODE_PROPERTIES ANPP,
		TEMP_NODE AN
		WHERE 
	  ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.TYPE_QNAME='asset' AND
    ANP.STRING_VALUE ='uPDF' AND ANPP.STRING_VALUE ='Entire PDF'  AND
    ANP.TYPE_QNAME='categories'  AND
 		ANP.NODE_ID IN(SELECT CHILD_ID 
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(GT.ISBN)) 
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID)
    )  CATEGORY_EXISTS,
    (SELECT LISTAGG(T1.STRING_VALUE, ',') WITHIN GROUP  (ORDER BY T1.STRING_VALUE)
       FROM 		TEMP_NODE_PROPERTIES T1
		WHERE   T1.TYPE_QNAME='name'  AND  (T1.STRING_VALUE LIKE '%pdf%'  AND  T1.STRING_VALUE NOT LIKE '%pod%') 
         AND  T1.NODE_ID IN (SELECT ANP.NODE_ID
		FROM 
		TEMP_NODE_PROPERTIES ANP,
    TEMP_NODE_PROPERTIES ANPP,
		TEMP_NODE AN
		WHERE 
	  ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.TYPE_QNAME='asset' AND
    ANP.STRING_VALUE ='uPDF' AND ANPP.STRING_VALUE ='Entire PDF' AND
    ANP.TYPE_QNAME='categories'  AND
 		ANP.NODE_ID IN(SELECT CHILD_ID 
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(GT.ISBN)) 
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID)))
     FINAL_DOCUMENTS
		FROM  GT_ADD_ISBNS GT;

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I would probably look at using the MERGE statement:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F

Specifically the WHEN MATCHED clause.  You will need to only use the inner select.  MERGE has problems when trying to UPDATE/INSERT into the same table that you select from.

There are many examples of MERGE out on the web.  I can't provide a copy/paste version because I still don't fully understand the requirements even though you have provided several samples of data and tables throughout all the previous related questions.
Avatar of chaituu

ASKER

I am getting below error when i execute whole update statement.but when i execute only select statement its giving correct results.


or: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

<code>
UPDATE gt_add_isbns
   SET (error_message, FILENAME) =
          (SELECT CASE
                     WHEN category_exists = 0 THEN 'category not found'
                     WHEN final_documents IS NULL THEN 'documents not found'
                  END,
                  CASE
                     WHEN final_documents IS NOT NULL THEN final_documents
                  END
             FROM (SELECT category_exists, final_documents
                     FROM (
SELECT  (SELECT COUNT(*)
            FROM
            TEMP_NODE_PROPERTIES ANP,
    TEMP_NODE_PROPERTIES ANPP,
            TEMP_NODE AN
            WHERE
        ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.TYPE_QNAME='asset' AND
    ANP.STRING_VALUE ='uPDF' AND ANPP.STRING_VALUE ='Entire PDF'  AND
    ANP.TYPE_QNAME='categories'  AND
             ANP.NODE_ID IN(SELECT CHILD_ID
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(GT.ISBN))
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID)
    )  CATEGORY_EXISTS,
    (SELECT LISTAGG(T1.STRING_VALUE, ',') WITHIN GROUP  (ORDER BY T1.STRING_VALUE)
       FROM             TEMP_NODE_PROPERTIES T1
            WHERE   T1.TYPE_QNAME='name'  AND  (T1.STRING_VALUE LIKE '%pdf%'  AND  T1.STRING_VALUE NOT LIKE '%pod%')
         AND  T1.NODE_ID IN (SELECT ANP.NODE_ID
            FROM
            TEMP_NODE_PROPERTIES ANP,
    TEMP_NODE_PROPERTIES ANPP,
            TEMP_NODE AN
            WHERE
        ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.TYPE_QNAME='asset' AND
    ANP.STRING_VALUE ='uPDF' AND ANPP.STRING_VALUE ='Entire PDF' AND
    ANP.TYPE_QNAME='categories'  AND
             ANP.NODE_ID IN(SELECT CHILD_ID
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(GT.ISBN))
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID)))
     FINAL_DOCUMENTS
            FROM  GT_ADD_ISBNS GT)));
</code>
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account