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;
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.
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>
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.