Home » SQL & PL/SQL » SQL & PL/SQL » single-row subquery returns more than one row [merged]
single-row subquery returns more than one row [merged] [message #401985] Thu, 07 May 2009 03:49 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I have the following sql when I run I am getting the error message "single-row subquery returns more than one row". Can anyone help me please.

UPDATE SING_STK_ALOC_MAST SET OTHER_REMARKS = 
(SELECT A.JOB_CODE FROM MSS_RO_JOBS A,MSS_RO_HDR B,SING_STK_ALOC_MAST C WHERE
C.RO_NO=B.RO_NO AND B.ID=A.ROHD_ID AND 
A.STS = 'I' AND
A.JOB_CODE IN ('T0102','T0103','T0104','T0105','T0106','T0107',
'T0501','T0502','T0503','T0504','T0505','T0506',
'W0101','W0102','W0103','W0104','W0105','W0106','W0201','W0202','W0203',
'W0204','W0205','W0206','W0301','W0302','W0303','W0304','W0305','W0306')
Re: single-row subquery returns more than one row [message #401986 is a reply to message #401985] Thu, 07 May 2009 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Change the where clause of the sub-query to only return a single row.
The error means what it says - you're trying to set other_remarks to multiple values.

Also don't you need a where clause on the update itself?
Re: single-row subquery returns more than one row [message #401992 is a reply to message #401985] Thu, 07 May 2009 03:59 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
The inner query


(SELECT A.JOB_CODE FROM MSS_RO_JOBS A,MSS_RO_HDR B,SING_STK_ALOC_MAST C WHERE
C.RO_NO=B.RO_NO AND B.ID=A.ROHD_ID AND 
A.STS = 'I' AND
A.JOB_CODE IN ('T0102','T0103','T0104','T0105','T0106','T0107',
'T0501','T0502','T0503','T0504','T0505','T0506',
'W0101','W0102','W0103','W0104','W0105','W0106','W0201','W0202','W0203',
'W0204','W0205','W0206','W0301','W0302','W0303','W0304','W0305','W0306')

is returning more than one row...
use distinct if you are getting duplicates

Please don't multipost.

[Updated on: Thu, 07 May 2009 04:10]

Report message to a moderator

Re: single-row subquery returns more than one row [message #402007 is a reply to message #401992] Thu, 07 May 2009 04:48 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I have attached the file with table create and inserting rows, please try and give me a solution for the sql query

I want to update the remarks in stk_aloc_mast from stk_ro_jobs linking through stk_ro_hdr with the following condition.

UPDATE STK_ALOC_MAST C SET C.REMARKS = 
(SELECT A.JOB_CODE FROM MSS_RO_JOBS A,MSS_RO_HDR B WHERE
C.RO_NO=B.RO_NO AND B.ID=A.ROHD_ID AND 
A.STS = 'I' AND
A.JOB_CODE IN ('T0102','T0103','T0104','T0105','T0106','T0107',
'T0501','T0502','T0503','T0504','T0505','T0506',
'W0101','W0102','W0103','W0104','W0105','W0106','W0201','W0202','W0203',
'W0204','W0205','W0206','W0301','W0302','W0303','W0304','W0305','W0306' and A.RO_NO=C.RO_NO);
Re: single-row subquery returns more than one row [merged] [message #402009 is a reply to message #401985] Thu, 07 May 2009 04:59 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Code that doesn't work doesn't count as a condition you can use.

You need to explain in English what the relationship is between the tables.

And next time you post create tables, lose the tablespace definition because we will not have your tablespaces.

I suspect the IN is fundamentally wrong - IN pretty much means multiple values - you don't want multiple values.
Previous Topic: how to encrypt column data
Next Topic: How to send refcursor result to a procedure as in parameter to get result
Goto Forum:
  


Current Time: Fri Dec 09 17:21:07 CST 2016

Total time taken to generate the page: 0.41631 seconds