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

chaituu chaituAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
The error is self explanatory.  You are trying to set a single column in a single row to the result of a query that is returning more than one value.

For example you have:
select mycol from mytable;

It returns two rows:
1
2

And you try:
update newtable set some_column=(select mycol from mytable);

The query returns two rows and you are updating a single value.  What should the value of some_column be, 1 or 2?

You also have no where clause on the UPDATE statement so you will be updating EVERY ROW in he entire table every time.  Given your previous questions on performance, that probably isn't what you want.

I suggested the MERGE statement for a reason.  Any reason you chose to use UPDATE over MERGE?
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
chaituu chaituAuthor Commented:
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>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here