You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Update Error - Single-row subquery returns more than 1 row

1 reply [Last post]
westendorf's picture
User offline. Last seen 10 years 3 days ago. Offline
Joined: 10/16/2010
Posts: 2

I'm working on an update. I am first getting the current data (NOW) and then comparing it to a saved file (LAST). I need to update this file which is called ps_uni_textbk_skew with the values from the NOW table for several fields for the keys that match the NOW table to the ps_uni_textbk_skew table. There are 5 keys (SUBJECT, CATALOG_NBR, CLASS_SECTION, STRM, SSR_TXBDTL_SEQNO) . This query works great if you remove the update stuff. I get 2 rows. But once I started adding the update around the query, it doesn't work anymore. I get the error 'single row subquery returns more than 1 row'. I'd like to keep the basic query the same because it works great and I understand it. Is there a way to write an update around it? Should I be using an IF EXISTS? I appreciate any help. Thanks, Mary

UPDATE PS_UNI_TEXTBK_SKEW S
SET (S.SSR_TXBDTL_TITLE, S.SSR_TXBDTL_ISBN, S.UBS_SKEW_NUM, S.PROCESS_DATE, S.UBS_REC_TYPE) =
(
SELECT NOW.SSR_TXBDTL_TITLE
,NOW.SSR_TXBDTL_ISBN
,0
,SUBSTR(SYSDATE,1,8)
,'C'
FROM
(
SELECT C.SUBJECT||C.CATALOG_NBR||C.CLASS_SECTION||C.STRM||B.SSR_TXBDTL_SEQNO AS TYINGKEEN
,C.CRSE_ID
,C.CRSE_OFFER_NBR
,C.STRM
,C.SUBJECT
,C.CATALOG_NBR
,C.CLASS_SECTION
,C.DESCR
,B.SSR_TXBDTL_SEQNO
,B.SSR_TXBDTL_TITLE
,B.SSR_TXBDTL_ISBN
FROM PS_CLASS_TBL C,
PS_SSR_CLS_TXB_DTL B
WHERE C.CRSE_ID = B.CRSE_ID
AND C.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR
AND C.STRM = B.STRM
AND C.CLASS_SECTION = B.CLASS_SECTION
) NOW,
(
SELECT SUBJECT||CATALOG_NBR||CLASS_SECTION||STRM||SSR_TXBDTL_SEQNO AS TYINGKEEL
,CRSE_ID
,CRSE_OFFER_NBR
,STRM
,SUBJECT
,CATALOG_NBR
,CLASS_SECTION
,DESCR
,SSR_TXBDTL_SEQNO
,SSR_TXBDTL_TITLE
,SSR_TXBDTL_ISBN
FROM PS_UNI_TEXTBK_SKEW
) LAST
WHERE NOW.TYINGKEEN = LAST.TYINGKEEL
AND (NOW.SSR_TXBDTL_TITLE <> LAST.SSR_TXBDTL_TITLE
OR NOW.SSR_TXBDTL_ISBN <> LAST.SSR_TXBDTL_ISBN))

WHERE S.SUBJECT||S.CATALOG_NBR||S.CLASS_SECTION||S.STRM||S.SSR_TXBDTL_SEQNO =
(SELECT LAST.TYINGKEEL
FROM
(
SELECT C.SUBJECT||C.CATALOG_NBR||C.CLASS_SECTION||C.STRM||B.SSR_TXBDTL_SEQNO AS TYINGKEEN
,C.CRSE_ID
,C.CRSE_OFFER_NBR
,C.STRM
,C.SUBJECT
,C.CATALOG_NBR
,C.CLASS_SECTION
,C.DESCR
,B.SSR_TXBDTL_SEQNO
,B.SSR_TXBDTL_TITLE
,B.SSR_TXBDTL_ISBN
FROM PS_CLASS_TBL C,
PS_SSR_CLS_TXB_DTL B
WHERE C.CRSE_ID = B.CRSE_ID
AND C.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR
AND C.STRM = B.STRM
AND C.CLASS_SECTION = B.CLASS_SECTION
) NOW,
(
SELECT SUBJECT||CATALOG_NBR||CLASS_SECTION||STRM||SSR_TXBDTL_SEQNO AS TYINGKEEL
,CRSE_ID
,CRSE_OFFER_NBR
,STRM
,SUBJECT
,CATALOG_NBR
,CLASS_SECTION
,DESCR
,SSR_TXBDTL_SEQNO
,SSR_TXBDTL_TITLE
,SSR_TXBDTL_ISBN

FROM PS_UNI_TEXTBK_SKEW
) LAST
WHERE NOW.TYINGKEEN = LAST.TYINGKEEL
AND (NOW.SSR_TXBDTL_TITLE <> LAST.SSR_TXBDTL_TITLE
OR NOW.SSR_TXBDTL_ISBN <> LAST.SSR_TXBDTL_ISBN)
);

bosmanjc's picture
User offline. Last seen 10 years 3 days ago. Offline
Joined: 07/23/2010
Posts: 14
Re: Update Error - Single-row subquery returns more than 1 row

Its kind of hard without being able to test w/ it, but i'm thinking that maybe you should work w/ MAX(SEQNO)